Monday, April 30, 2012 6:45 PM
Environment: Windows Vista Enterprise SP2
Tools: SQL Server Management Studio 2008
Symptom/Problem: A problem, transferring table contents (from source to target), occurs periodically. There is a "kink" in the [server.database 1] reconciliation processes, where specific students enrolled in an Intermediate/Advanced/Refresher course do not get applied to the [server.database 2]. Transferring process of data from source to target tables is somewhat broken, which 95% of students records are being transferred, while 5% of the students' records are not transferred. We don't know whether the data transfer is conducted by stored procedure, T-SQL.
Question: How I would check if the data in (source.server.db.table1) is being copied/transferred to target.server.db.table2. In other words, compare the destination of Server.Database with the target of the Server.Database tables. If the data is not being copied then copy all records (no redundant data is allowed). If students records copied then no action.
- Edited by sandra V O Monday, April 30, 2012 9:28 PM
Monday, April 30, 2012 6:52 PM
I guess the first thing I would do is figure out what the periodic process is.
Start by looking at the sql agent jobs and see if that is what is scheduling the process.
If that is the source then look at the job steps to determine wether it is SQL, stored procs, ssis, etc
Then go from there.
Monday, April 30, 2012 9:19 PMThanks Chuck. I don't have admin access in my local machine and I can not check or create SQL job agent.
Monday, April 30, 2012 9:26 PMThen you're probabally not going to have much luck figuring it out. You need to get a DBA from your organization involved
Monday, April 30, 2012 9:30 PM
Thank Chuck. DBA was a contractor who did the Job.I am trying to find a proactive solution using T-SQL
How I would check if the data in (source.server.db.table1) is being copied/transferred to target.server.db.table2. In other words, compare the destination of Server.Database with the target of the Server.Database tables. If the data is not being copied then copy all records (no redundant data is allowed). If students records copied then no action.
Monday, April 30, 2012 9:37 PM
Assuming the data is supposed to match 100%, easiest way would be to use a data compare tool (redgate, AdeptSQL, SQL Server Data tools). They will all do a compare and sync if you wish
Doesn't sound like you have enough database access to set up a linked server - which is the only way I can think of to do a pure TSQL data compare between tables on different servers.
- Edited by Chuck Pedretti Monday, April 30, 2012 9:38 PM
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Tuesday, May 08, 2012 12:19 AM
Monday, April 30, 2012 9:51 PM
Many thanks for the rapid response Chuck. I like your idea of using a data compare tool (redgate, AdeptSQL, SQL Server Data tools). I'll consider this approach in the near future or working in development environment.
I have a DBO access to the database on several servers. I would like to resolve the issue by comparing data between two tables. Then, it checks for the data. Next sync all missing data from the source to the target.
Tuesday, May 01, 2012 2:14 AM
SQL Server Tablediff utility can be used as a data comparing tool.
Please see http://msdn.microsoft.com/en-us/library/ms162843.aspx for more options of the utility.