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 - Page 2

By Muthusamy Anantha Kumar aka The MAK



Step 3

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





When the compare.bat file is executed, it bcp's out the query results from the first server to c:\compare\outfile1.txt, then it bcp's out the query results from the second server to c:\compare\outfile2.txt. Then it finds the difference between c:\compare\outfile1.txt and c:\compare\outfile2.txt using MS-DOS's fc.exe. It also create log file and gathers information as shown below. [Fig 1.4]



"Compare Query results started" 
Sun 10/03/2004 
 2:28a
.... 
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
FC: no differences encountered

"Compare Query results Completed" 
Sun 10/03/2004 
 2:30a


Fig 1.4

It also displays whether the query results are same. Fig 1.5]


Fig 1.5

Parameters of Compare.bat explained

Compare.bat sql sa yeahright ebony sa yeahright2 "select * from sysobjects 
  where name <>'sysobjects'" x.txt

Compare.bat = batch file name

Sql = First SQL server box

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 both SQL server boxes.

Yeahright2 = Password for Sa

x.txt = Log File name

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


Fig 1.6


Fig 1.7

Conclusion

This article has introduced a method on how to take advantage of an MS-DOS batch file and SQL Server utility BCP.exe to compare the query results executed on two different servers. Part II of this article will examine how to compare results of the same query on the same or different servers, but on different databases.

» See All Articles by Columnist MAK



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