How can we help you today? How can we help you today?

Automation of Data Comparision

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
Nagaraj
0

Comments

17 comments

Sort by
  • AlexYates
    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
    0
  • Nagaraj125
    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.
    Nagaraj125
    0
  • AlexYates
    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/
    AlexYates
    0
  • Nagaraj125
    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
    0
  • AlexYates
    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.
    AlexYates
    0
  • Nagaraj125
    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


    Nagaraj125
    0
  • AlexYates
    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.
    AlexYates
    0
  • Nagaraj125
    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:


    Nagaraj125
    0
  • Alex B
    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:
    ​

    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
    Alex B
    0
  • Nagaraj125
    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.


    Nagaraj125
    0
  • Alex B
    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
    Alex B
    0
  • Nagaraj125
    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
    0
  • Alex B
    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
    Alex B
    0
  • Nagaraj125
    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.
    Nagaraj125
    0
  • Alex B
    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
    Alex B
    0
  • Nagaraj125
    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.
    Nagaraj125
    0
  • Alex B
    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
    Alex B
    0

Add comment

Please sign in to leave a comment.