Automation of Data Comparision

Nagaraj125Nagaraj125 Posts: 9 New member
edited August 26, 2019 12:14PM in SQL Data Compare
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

Best Answers

Answers

  • Nagaraj125Nagaraj125 Posts: 9 New member
    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.
  • AlexYatesAlexYates Cambridge, UK Posts: 263 Rose Gold 2
    edited 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/
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants and Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • Nagaraj125Nagaraj125 Posts: 9 New member
    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.

  • Nagaraj125Nagaraj125 Posts: 9 New member
    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


  • AlexYatesAlexYates Cambridge, UK Posts: 263 Rose Gold 2
    edited August 30, 2019 10:17AM
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants and Data Relay
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • Nagaraj125Nagaraj125 Posts: 9 New member
    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:


  • Alex BAlex B Posts: 1,027 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Nagaraj125Nagaraj125 Posts: 9 New member
    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.


  • Alex BAlex B Posts: 1,027 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Nagaraj125Nagaraj125 Posts: 9 New member
    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"


  • Nagaraj125Nagaraj125 Posts: 9 New member
    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.
  • 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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Nagaraj125Nagaraj125 Posts: 9 New member
    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.
  • Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.