Compare Query Results - Part II - Page 2

Step 3

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

When the compare2.bat file is executed, it bcp's out the query results from the first server to c:\compare\outfile1.txt and it bcp's out the query results from the second server (in this case it is the same server) to c:\compare\outfile2.txt. Then it finds the difference between c:\compare\outfile1.txt and c:\compare\outfile2.txt using MS-DOSs fc.exe. It also creates a log file and gathers information as shown below. [refer Fig 1.4]

"Compare Query results started" 
Sun 10/03/2004 
 3:19a
.... 
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
"Compare Query results Completed" 
Sun 10/03/2004 
 3:19a

In addition, it displays whether the query results are same. [refer fig 1.5]

Parameters of Compare2.bat explained

Same server Different database

compare2 sql sa yeahright sql sa yeahright "select top 10 * 
  from master..sysobjects where name ='sysobjects'" "Select top 10 * 
  from msdb..sysobjects where name = 'sysobjects'" x.txt

Different server same database

compare2 sql sa yeahright ebony sa yeahright2 "select top 10 * 
  from master..sysobjects where name ='sysobjects'" "Select top 10 * 
  from master..sysobjects where name = 'sysobjects'" x.txt

Different server different database

compare2 sql sa yeahright ebony sa yeahright2 "select top 10 * 
  from master..sysobjects where name ='sysobjects'" "Select top 10 * 
  from msdb..sysobjects where name = 'sysobjects'" x.txt

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

Conclusion

Part I and Part II of "Compare Query Results" has discussed how to compare the query results executed on two different servers or on the same server and on two different databases. Both articles take advantage of a MS-DOS batch file and SQL Server utility BCP.exe

» See All Articles by Columnist MAK