October 14, 2020 at 6:45 am
Easiet way to we capture data changes to a table.(without using CDC).
I need to capture previous data and the new data.
My setup process:
Table A is reference table
Table B is copy of reference table. It gets deleted and populated everyday.
I need to send out an HTML email capturing the entire table highlighting the changed values.
How can I achieve this.
Thanks
October 14, 2020 at 10:38 am
I can see 3 tasks here:
- capture and record data changes;
- build a report comparing current data with the previous versions of it;
- generate HTML file out of that report and send it out.
Which of these task you need to be helped with?
_____________
Code for TallyGenerator
October 14, 2020 at 1:22 pm
This should suffice for now ->capture and record data changes.
But looks like I am stumped prior to this task.
I built multiserver administrative servers.
My principal server has 1 job which I manually executed once;scheduled at 6:00 AM. Once I did this it created 1 job each on the other 3 mirror servers.So now I have 1 job per mirror server scheduled to 6:00 AM.The job is a simple tsql script with 3 insert statements
insert into A values('A',1)
insert into A values('B',2)
insert into A values('C',3)
But somehow the table A has 6 rows inserted(when it should only have 3 rows) on all 3 servers making it look like the mirror server jobs executed twice, but the job histories show one 1 execution on all 3 mirror servers.
Any ideas on what seems to be going on here.
I will be creating 1 more table ReferenceTable on all the 3 mirror servers which will have similar data as table A .
The table A on the mirror servers will deleted everyday and populated everyday.
I need to compare the ReferenceTable on the mirror servers with table A on the respective mirror servers and come up with a report showing discrepancies. I do not want to use CDC or triggers.
ReferenceTable and Table A will have same definitions on all mirror servers.
After capturing the discrepancies I need to generate a report showing the same.
I do not want a report if there are no discrepancies.
OR
I can centralize all the table's A data from all servers into the master server where I will compare with the similarly built ReferenceTable as stated above on the master server and generate 1 report showing discrepancies for all servers instead of generating 1 report per server if discrepancies exist.
OR
How can schedule a query running in Central Management Server. Is there a sql way of doing it or does it have go the powershell way
I know the entire process I have explained can be achieved using SSIS and I am well aware of the process..looping through servers..centralizing the data..builld your referenceTable on the server where data has been centralized and then compare and report the discrepancies.But I do not want this option.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply