SQL Server: Compare Query Results - Part I - Page 2

Step 3

Execute the batch file by passing right parameters as shown in Fig 1.3

When the compare.bat file is executed, it bcp's out the query results from the first server to c:\compare\outfile1.txt, then it bcp's out the query results from the second server to c:\compare\outfile2.txt. Then it finds the difference between c:\compare\outfile1.txt and c:\compare\outfile2.txt using MS-DOS's fc.exe. It also create log file and gathers information as shown below. [Fig 1.4]

"Compare Query results started" 
Sun 10/03/2004 
 2:28a
.... 
Attempting to delete 
  "C:\Compare\Outfile1.txt"  
Delete process Completed  
.... 
Attempting to delete 
  "C:\Compare\Outfile2.txt"  
Delete process Completed  
.... 
Attempting to delete 
  "C:\Compare\DiffFile.txt"  
Delete process Completed  
.... 
.... 
"Attempting to BCP out the given query from the server :: sql" 

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1
.... 
"Attempting to BCP out the given query from the server :: ebony" 

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1
"Comparing  "C:\Compare\Outfile1.txt" and "C:\Compare\Outfile2.txt" " 
"Finding the difference" 

---------- C:\COMPARE\DIFFFILE.TXT
FC: no differences encountered

"Compare Query results Completed" 
Sun 10/03/2004 
 2:30a

It also displays whether the query results are same. Fig 1.5]

Parameters of Compare.bat explained

Compare.bat sql sa yeahright ebony sa yeahright2 "select * from sysobjects 
  where name <>'sysobjects'" x.txt

Compare.bat = batch file name

Sql = First SQL server box

Sa = Login for the First SQL Server box

Yeahright = Password for Sa

Ebony = Second SQL server box

Sa = Login for the second SQL Server box

"select ...." = Query to be executed on both SQL server boxes.

Yeahright2 = Password for Sa

x.txt = Log File name

You can also get the usage example information by executing compare.bat without any parameters or with /? As parameter. [Fig 1.6 and Fig 1.7]

Conclusion

This article has introduced a method on how to take advantage of an MS-DOS batch file and SQL Server utility BCP.exe to compare the query results executed on two different servers. Part II of this article will examine how to compare results of the same query on the same or different servers, but on different databases.

» See All Articles by Columnist MAK