Compare Query Results - Part II

Tuesday Nov 16th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

Part I of this article discussed how to compare the query results executed on two different servers. In part II, we are going to discuss how to compare results of the same query on the same or different server but on different databases by taking advantage of an MS-DOS batch file and SQL Server utility, BCP.exe


In part I of this article, we discussed how to compare the query results executed on two different servers. In part II, we are going to discuss how to compare results of the same query on the same or different server but on different databases. This article takes advantage of a MS-DOS batch file and SQL Server utility BCP.exe



Pre-Requisite


SQL Server 2000 client and latest service pack should be installed on the machine where you are running the batch file.



Step 1


Create the folder, c:\compare, as shown in Fig 1.1



Fig 1.1

Step 2

Create c:\compare\compare2.bat and copy and paste the code below into it. Save the file. [refer Fig 1.2]

@echo off
REM Objective: To compare results of a query on two different server
REM Created by: MAK
REM Date: Oct 2, 2004
Echo ...
Echo ...
Echo ...

REM Check parameters
if "%1"=="" Goto noparmErr
if "%1"=="/?" Goto Help

REM assign variables
set server1=%1
set login1=%2
set pwd1=%3
set server2=%4
set login2=%5
set pwd2=%6
set Query1=%7
set Query2=%8
set LogFile=%9
set outfile1="C:\Compare\Outfile1.txt"
set outfile2="C:\Compare\Outfile2.txt"
set DiffFile="C:\Compare\DiffFile.txt"

Echo "Compare Query results started" >%LogFile%
date/t >> %LogFile%
time/t >> %LogFile%

Echo .... >> %LogFile%
Echo Attempting to delete %outfile1%  >> %LogFile%
IF EXIST %outfile1% (del /Q %outfile1% ) ELSE echo %outfile1% not found. Skipping delete.
Echo Delete process Completed  >> %LogFile%
Echo .... >> %LogFile%
Echo Attempting to delete %outfile2%  >> %LogFile%
IF EXIST %outfile2% (del /Q  %outfile2% ) ELSE echo %outfile2% not found. Skipping delete.
Echo Delete process Completed  >> %LogFile%
Echo .... >> %LogFile%
Echo Attempting to delete %DiffFile%  >> %LogFile%
IF EXIST %DiffFile% (del  /Q %DiffFile% ) ELSE echo %DiffFile% not found. Skipping delete.
Echo Delete process Completed  >> %LogFile%
Echo .... >> %LogFile%

:BCP1
Echo .... >> %LogFile%
Echo "Attempting to BCP out the given query from the server :: %Server1%" >> %LogFile%
bcp.exe %Query1% QUERYOUT %outfile1% -S%server1% -U%login1% -P%pwd1% -c  >>%LogFile%

:BCP2
Echo .... >> %LogFile%
Echo "Attempting to BCP out the given query from the server :: %Server2%" >> %LogFile%
bcp.exe %Query2% QUERYOUT %outfile2% -S%server2% -U%login2% -P%pwd2% -c  >>%LogFile%

:Compare
Echo "Comparing  %outfile1% and %outfile2% " >> %LogFile%
Fc.exe %outfile1% %outfile2% > %DiffFile%

:Search
Echo "Finding the difference" >> %LogFile%
find.exe "FC: no differences encountered" %DiffFile% >> %LogFile%
if "%errorlevel%"=="0" echo "Both results are same. 
  Check the file %DiffFile% for differences and Log file %LogFIle%" 
if "%errorlevel%"=="1" echo "Results are differnt. Check the file %DiffFile% for differences" 

Goto END

:noparmErr
Echo No Parameters specified..... Usage example as follows
echo ...
Echo %0 Server1 Login1 Password1 Server2 login2 password2 "Query1" "Query2" x.txt
Goto END

:Help
Echo Usage help: example as follows
echo ...
Echo Objective: To compare results of a query on two different server
Echo Created by: MAK
Echo %0 Server1 Login1 Password1 Server2 login2 password2 "Query1" "Query2" x.txt
Goto END

:END
Echo "Compare Query results Completed" >>%LogFile%
date/t >> %LogFile%
time/t >> %LogFile%

Download compare2.bat.


Fig 1.2

Step 3

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

Click for larger image

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


Fig 1.4

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


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
Compare2.bat = batch file name
Sql = First SQL server bo
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 the first SQL server box.
"select ...." = Query to be executed on the second SQL server box.
Yeahright2 = Password for Sa
x.txt = Log File name

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]


Fig 1.6


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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved