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 16, 2004

Compare Query Results - Part II - 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 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



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