Automation of Data Comparision

Nagaraj125 · August 26, 2019 12:09PM

Can i automate data comparison between the two tables using red gate data compare 13? If so could you please assist how to automate it

AlexYates · August 26, 2019 4:42PM

You'll want to use the SQL Compare command line. There's a getting started page here:
https://documentation.red-gate.com/sc13/using-the-command-line/command-line-basics

There are some simple examples here:
https://documentation.red-gate.com/sc13/using-the-command-line/simple-examples-using-the-command-line

And full documentation of all the command line switches is here:
https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line

Once you've figured out the command you need, you should be able to schedule that using your automation tool of choice. If you don't have one, sticking that command into a batch file and running it from Windows Scheduler would do what you ask.

But I'd love to know exactly what are you trying to achieve? Depending on the answer you may be better off looking at SQL Change Automation.

AlexYates · August 28, 2019 8:32AM

My humble apologies. I misread the question and sent you the SQL Compare (schema comparing) command line docs rather than the SQL Data Compare (data comparison).

https://documentation.red-gate.com/sdc13/using-the-command-line/command-line-syntax

There are some simple examples here:
https://documentation.red-gate.com/sdc13/using-the-command-line/examples-using-the-command-line/simple-examples-using-the-command-line

It might be because you are cd'ing to the SQL Compare directory but then calling SQL Data Compare (which lives in a different directory).

Try adding the path to SQL Compare / Data Compare to your PATH environment vartiable. That should negate the need for all the CD stuff. Also, have you tried the syntax:

sqldatacompare /project:"C:\Users\UserName\Desktop\Project.sdc" >> "D:\Results\log.txt"

Beyond that, it would help if you could share the error message.

Alex B · August 30, 2019 12:54PM

Hi @Nagaraj125,

Ah, wait, it's because it's a regular expression for the /Include - it's including everything that contains any of the letters in [JobType] rather than looking for the word JobType.

You'll either want to change it to escape the square brackets like this:

<b>/Include:table:\[JobType\] /Verbose >> "D:\Results\log1.txt"</b>

or you'll need to remove them altogether like this:

<b>/Include:table:JobType /Verbose >> "D:\Results\log1.txt"</b>

Kind regards,
Alex

Nagaraj125 · August 27, 2019 4:09AM

Thanks Alex for the quick response. I would like to compare few tables from two different databases (Pord and Test) using red gate data compare 13. And same I would like to automate using the batch file.

AlexYates · August 27, 2019 9:32AM

Cool, is this for reporting? If so I recommend you check out the /report switch:
https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line?_ga=2.261896781.1791667307.1566837276-1706698906.1534783619#Switchesusedinthecommandline-/Report:%3Cfilepath%3E

If this is to set up some sort of automated deployment, remember to use the /synchronise switch:
https://documentation.red-gate.com/sc13/using-the-command-line/switches-used-in-the-command-line?_ga=2.261896781.1791667307.1566837276-1706698906.1534783619#Switchesusedinthecommandline-/Synchronize

However, it sounds like what you want to do is track which changes are made and maybe which changes are associated with the development of this or that feature.

If that's the case, I strongly recommend you consider looking at Redgate SQL Source Control and basing your change management and deployment processes off source control. This will make it much easier for you to track this stuff and deploy with confidence:
https://www.red-gate.com/products/sql-development/sql-source-control/

Nagaraj125 · August 28, 2019 6:36AM

Thanks alex for looking in to this.

As mentioned in my last post I wanted to compare tables from two different DB servers.

Hence I used below syntax in my batch file (.bat) but it didn't work out.

C:

Cd C:\Program Files (x86)\Red Gate\SQL Compare 13

sqldatacompare /server1:UATServerName /database1:DatabaseName
/server2:TestServerName /database2:DatabaseName

/Include: table:[Product] / Verbose

/Export: "D:\Results"

Also I tried to automate using the already saved project file using below syntax:

sqldatacompare /project:"C:\Users\UserName\Desktop\Project.sdc"
/Out: "D:\Results\log.txt"

But in both the examples it didn't work. Could you please confirm if i am missing anything on the above syntax.

Nagaraj125 · August 29, 2019 5:29AM

Hi Alex,

I have created one project and tried to automate the comparison using below syntax in batch file as suggested by u:

sqldatacompare /project:"C:\Users\UserName\Desktop\Project.sdc" >> "D:\Results\log.txt"

However the out file generated from above action in the "Results" folder is not giving any conclusive comparison details. It shows only minimal details.

Attached is the log file generated for your reference. Could you please assist me on the same if i have missed on anything.

Thanks,
Nagaraj S

AlexYates · August 30, 2019 10:16AM

Am I reading it correctly, it literally only says:

<div>SQL Data Compare Command Line for DLM Automation V13.8.0.12547

</div><div>==============================================================================</div><div>
Copyright Copyright c Red Gate Software Ltd 2019</div><div><br></div><div>
DLM Automation: in trial, expires 2019/09/26 05:17:58 +05:30</div><div>
Automation License: in trial, expires 2019/09/26 05:17:58 +05:30</div><div>
Registering databases</div>

That doesn't look right to me. I would have expected either some feedback about the results, or some error message. Unless the process was exited before it had finished?

I've emailed the official Redgate support team and asked them to have a look at this thread.

Nagaraj125 · August 30, 2019 10:50AM

Thanks Alex again, actually above error happened due to space issue which is resolved now as I got the correct log file with count data.

However can you please assist me for the scenario where If I want to compare table (not just the count entire table data) what will be syntex. I am trying with below syntax but not much luck:

sqldatacompare /server1:servername\instance /database1:Testdatabase
/server2:servername\instance /database2:UATDatabase

/Include: table:[Products] / Verbose >> "D:\Results\log.txt"

Error says:

Image: https://us.v-cdn.net/6029854/uploads/editor/am/6z8uk0ws4gzu.png

Alex B · August 30, 2019 11:55AM

Hi @Nagaraj125,

The first issue I can see is that you have tried using SqlCompare.exe first from the \SDC\ folder. The last section you have switched to using SqlDataCompare.exe correctly, but then the second issue below comes into play.

The second issue is that you are pasting a command split across multiple lines into the command prompt window, which will run each line on it's own. You will need to have the command be one continuous line OR alternatively you will need to use the caret character (Shift+6) to span the command across multiple lines in the command prompt when you paste it in. If you do this, you will see the word "More?" at the left of the screen:

Image: https://redgatesupport.zendesk.com/attachments/token/hxEOTTMBzoOZlNnPBgkBROlOd/?name=inline452231478.png

So your command would look like:

sqldatacompare /server1:servername\instance /database1:Testdatabase ^
/server2:servername\instance /database2:UATDatabase ^
/Include: table:[Products] /Verbose >> "D:\Results\log.txt"

I hope that helps!

Kind regards,
Alex

Nagaraj125 · August 30, 2019 12:11PM

HI @Alex B

Thanks for looking into this.

I tried with above mentioned syntax with table name used as "JobType". But got the error in log file as attached. Kindly suggest on the same.

Image: https://us.v-cdn.net/6029854/uploads/editor/fa/ay1zwwqsszd9.png

Alex B · August 30, 2019 12:29PM

Hi @Nagaraj125,

Looking at the command I put above, I realize that I've left a space between the "/Include:" and "table:[Products]" which may be causing this. If you change the last line of the command above to:

/Include:table:[JobType] /Verbose >> <span>"D:\Results\log.txt"</span>

Does it then work? If not, I'd need you to put the whole command you are using (obfuscating any passwords) so I can see what else may be going on.

Kind regards,
Alex

Nagaraj125 · August 30, 2019 12:46PM

Hi @Alex B

Thanks It worked. But I wanted to compare only one table called 'Jobtype'. But even though i used Include command it compared all the tables available in the database.

Also I wanted to comparison of actual table data not just the count. How to accomplish that? Please suggest me to actual data comparison of the table data.

Below is command used and attached is the result log.

sqldatacompare /server1:BANVS-DEVDB12-3\SQL2012 /database1:BCR ^
/server2:BANVS-DEVDB12-3\SQL2012 /database2:BCRDev ^
/Include:table:[JobType] /Verbose >> "D:\Results\log1.txt"

Nagaraj125 · August 30, 2019 2:57PM

Hi @Alex B

Thanks again. Now I got out put related to only one table.

But this time also I got output related to only the count of that table. I am looking for complete table comparison which includes table data and expecting the differences in the log file (same as when table is selected for data comparison in SQL Data Compare 13 UI) will be generated.

Could you please help with me this as well.

Alex B · September 2, 2019 9:41AM

Hi @Nagaraj125,

The individual differences are not written to the console (I believe due to the potential to be quite numerous).

You will need to include the /Export:<directory> switch (see this page) which will create comparison files - one called "Results Summary.csv" which has the count information and the others are listing the actual differences in each table involved (only that have differences I believe).

Kind regards,
Alex

Nagaraj125 · November 19, 2019 6:05AM

Alex B said:

Hi @Nagaraj125,

The individual differences are not written to the console (I believe due to the potential to be quite numerous).

You will need to include the /Export:<directory> switch (see this page) which will create comparison files - one called "Results Summary.csv" which has the count information and the others are listing the actual differences in each table involved (only that have differences I believe).

Kind regards,
Alex

Hi Alex,

Sorry for the late reply, its been while now.

Can you please suggest where can I use switch command to compare the data of two tables.

Alex B · November 25, 2019 4:53PM

Hi @Nagaraj125,

This documentation page goes over some simple examples on the command line: https://documentation.red-gate.com/sdc/using-the-command-line/examples-using-the-command-line/simple-examples-using-the-command-line

And this page goes over selecting a single table for comparison:
https://documentation.red-gate.com/sdc/using-the-command-line/examples-using-the-command-line/example-selecting-single-tables-for-comparison

I hope this helps!

Kind regards,
Alex

Automation of Data Comparision

Best Answers

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk