Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 3, 2004

SQL Server: Compare Query Results - Part I

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date