When you don't have any sql server analysis tools to hand comparing the data from two tables can be a pain, especially when those tables are in different databases, or even completely different servers. I've found myself in this position several times so eventually I decided to just write myself a small VBScript which can extract the data for me and load up my favourite diff tool to compare the contents.

Using the VB Script:

First you need to set the constants at the top of the vbs file to tell it where to get the data and where to find the compare engine exe:

'Table 2--------------------------------------------------------------------------------------------

const t2Server          = "ExampleSqlServer2"

const t2Database        = "MyExampleDatabase2"

const t2Table           = "ReplicatedCustomerOrders"

const t2Columns         = "OrderID, ProductID, OriginalQty"

const t2WhereClause     = "ProductID is not null"

    ' comment out one or the other of below depending on how you plan to connect. Default is windows authentication

const t2Security        = "Trusted_Connection=Yes"

'const t2Security       = "User ID =enterusernamehere; Password=mypassword "

'------------------------------------------------------------------------------------------------

 

'Export Options

const defaultExportLocation = "C:\databasecompareexports\"

 

'Comparison Application-------------------------------------------------------------

const comparisonEngine = "C:\windiff.exe"

'-------------------------------------------------------------------------------------------

Secondly you need to make sure the execution string for the comparison tool is correct, I’ve tested it working with Windiff.

sCompare = comparisonEngine & " " & defaultExportLocation & "1.output " & defaultExportLocation & "2.output"

wShell.Run(sCompare)

That's it! If you're using it with WinDiff it will work using only step 1!

Debugging

Throughout the script there are calls to a logging function which is present at the bottom of the script. Currently all this does is write a line to the windows application event log.

sub LogMyEvent(eventType, eventMessage)

                wShell.LogEvent eventType, eventMessage

end sub

You can find the event log by right clicking my computer, going to 'Manage' then goto 'Event Viewer' 'Application'. Because the script calls this procedure and not the shell logger directly you can replace this with whatever process you wish! Hooray!

Remember that VB scripts have different error handling capabilities than regular VB6 or VBA, in other words you can't GOTO anywhere on error, it either breaks or carries on. So you have to manually check for errors at every important point. You can see this pattern throughout the script.

I'm storing the file as a .txt file, don't ever trust .vbs files you download from the internet, they can do some nasty things to your computer! While you can trust my script, I'd recommend checking ALL VB scripts out first with notepad++ before using them.

If you’re not familiar with comparison tools check out Windiff (free), Compare It! (not free, but the best) and WinMerge (also free).