Problem: 5% of transferring table contents are not being loaded/ copied issue

Question

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.

Please advise

Answer 1

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.

---

Chuck

Answer 2

Thanks Chuck. I don't have admin access in my local machine and I can not check or create SQL job agent.

Answer 3

Then you're probabally not going to have much luck figuring it out.  You need to get a DBA from your organization involved

---

Chuck

Answer 4

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.

Please Advise

Answer 5

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.

---

Chuck

Answer 6

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.  

Answer 7

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.