In the Database administration world, it is often necessary
to run a query on the production box and then run the same query on the QA
or UAT box and compare the results. In this article, I would like to introduce a
method that takes advantage of an MS-DOS batch file and SQL Server utility,
BCP.exe, to compare the query results executed on two different servers.
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\compare.bat and copy and paste the below code into it. Save the
file. [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 Query=%7
set LogFile=%8
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 %Query% 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 %Query% 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 “select top 10 * from
master..sysobjects” 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 “select top 10 * from
master..sysobjects” x.txt
Goto END
:END
Echo “Compare Query results Completed” >>%LogFile%
date/t >> %LogFile%
time/t >> %LogFile%
Download compare.bat
Fig
1.2