Compare Query Results - Part II

November 16, 2004

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

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.