Comparing Tables in SQL Server 2005

[Catadmin]

Okay. Here's another tool in SQL Server 2k5 I just learned about. It's the tablediff utility (and can be found in BOL under that keyword).

TableDiff is a command utility that allows you to compare tables that have been replicated to verify whether or not the tables have been synced. However, I'm wondering if it can be used between servers with the same tables but not involved in replication.

I did try using it from a command prompt and from the SQLCmd prompt, but both attempts failed. Not sure if this is a directory thing or not (I was working in my root drive). Still, it's an interesting utility I thought people might want to know about. If I can get it working, I'll post. If anyone else got it working, please let me know.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Aha! It is a directory thing. I had to go to C:\Program Files\Microsoft SQL Server\90\Com\ to get the tablediff.exe to work properly. Change the drive appropriately for where ever you installed your SQL Server.

After I ran the command, it listed all my parameters and sat without a prompt as it tried to connect. Then I got an error "Unable to connect to MyDestServer". At this point, I'm unsure if this is because I don't have replication set up at all or because MyDestServer isn't set up as a linked server with MySourceServer. Either way, the tool is there and does actually try to work.

Try being the operative word, of course. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

I used it one time. It was OK when I used it. Here is the command I used

C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourcetable blah1 -souceserver mycomputernm -sourcedatabase master -destinationserver mycomputernm -destnationdatabase master -destinationtable blah2

 

[Catadmin]

Are you running replication or are your servers linked?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[manmaria]

Both the dbs are in the same server. One is like a UAT db and another is PROD db

 

[Catadmin]

Aha. I was trying to run my compare with 2 different servers. So, it looks like this tool can work as a basic data compare tool even without Replication.

COOL!



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"