What are the challenges you face when working across database platforms? Take the survey
Options

Questions on how to programmatically sync w/ where clause

j.salmonj.salmon Posts: 44 Bronze 2
Brief background on what I am trying to do:
I need to sync a large database. The large majority of tables are small and can be copied in full (can be done with a batch file). There are about 12 tables which have massive amounts of rows. For these 12 tables, I need to be able to use a where clause. I also need to be able to do this programmatically.

Just a couple high level questions:
Do SQL Data Compare batch files currently allow for a where clause option?
Is the only option to do this is by using data compare's APIs?

I am just trying to figure out what my options are and any recommendations.

Comments

  • Options
    Hi,

    There's two ways of setting a WHERE clause, depending on how you're running Data Compare.

    If you're using the .NET API, then you have a Where property (taking a WhereClause object) on the TableMapping. Obviously you can set this as flexibly as you like.

    If you're using the Data Compare command line, you can't set a WHERE clause directly, but you can specify a project created using the graphical interface, which does allow you to set these. This should work if you're always wanting to use the same WHERE clause for each run, but maybe not if you need to dynamically generate it.

    I hope that helps - if you've got any questions, please let me know.

    Regards,
    Robert
    Robert Chipperfield
    Red Gate
  • Options
    j.salmonj.salmon Posts: 44 Bronze 2
    Hi Robert and thanks for the quick response.

    I was unaware about creating and then executing a .sdc file from the command line, so I gave that a shot.

    I created a project. I selected only one table (leaving all other tables unchecked). With this one table, there is a where clause which selects rows based on date; in this scenario exactly 10 rows are returned. When running the project through the data compare GUI, the comparison is instantaneous. When running the same query through SQL server manager, the query execution time is 00:00. I saved the project .sdc. My problem is when I run the .sdc through the command line.

    I start data compare with this: sqldatacompare.exe /project:"c:\DataCopyTest.sdc" /verbose

    Data compare starts, but never finishes. It just sits at "Comparing databases". Am I doing something wrong? I looked at the .sdc file and have verified that no other tables are listed.
  • Options
    Hi,

    I think there was a bug in version 6.0 of the command line that meant your GUI table selections weren't honoured by the command line, but other settings (correctly) were. This has been fixed, and will be in the 6.1 release of Data Compare (due early Q1 2008).

    For now, if you just want to compare one table, you can use the /include switch in addition to the /project switch:
    sqldatacompare.exe /project:"MyProject.sdc" /include:MyTable
    

    If it appears to still hang, I wonder if you could check the CPU usage of Data Compare to see if it's really doing anything or not. It might also be worth checking you haven't got an evaluation screen popped up somewhere in the background if you're in the trial period at the moment.

    Hope that helps,
    Robert
    Robert Chipperfield
    Red Gate
  • Options
    j.salmonj.salmon Posts: 44 Bronze 2
    Ok, following your instructions........here's what I did.

    I ran the following
    sqldatacompare.exe /project:"c:\VinylRemake.sdc" /include:Table:\[VinylRemakeHistory\]

    This runs the data compare only for the table VinylRemakeHistory. This runs quickly and outputs the results as expect.

    Registering databases
    Mapping
    Comparing databases
    Finished

    Summary Information
    =====================================================================================================================================================================================================================================
    DB1 =
    DB2 = alsddb4.aldata002

    Object type Name Records DB1 DB2
    Table [dbo].[VinylRemakeHistory] 10 >>
    Table [dbo].[VinylRemakeHistory] 52960 <<

    If I add a sync to the end of the previous cmd:

    sqldatacompare.exe /project:"c:\VinylRemake.sdc" /include:Table:\[VinylRemakeHistory\] /sync

    The sync. fails. I get the following error:
    Error: Synchronization of 'Server1.TestDB1' and 'Server1.TestDB2' failed: The DELETE statement conflicted with the REFERENCE constraint "FK_VinylRemakeHistoryProductionLine_VinylRemakeHistory". The conflict occurred
    in database "TestDB2", table "dbo.VinylRemakeHistoryProductionLine", column 'VinylRemakeHistoryID'.

    In the project, I have specified for it to drop everything in the sync behavior (foreign keys, prim keys, triggers, etc). If I reopen the .sdc in data compare, the settings are retained, but they don't appear to be used with the command line compare?
  • Options
    Hi,

    The "disable foreign keys" option only disables them temporarily during the insert / update / delete operations - it doesn't leave them disabled afterwards. This means if you're synchronizing a table that has dependencies on tables you're not synchronizing, you can end up with errors.

    This is the same behaviour as if you try and synchronize using the GUI.

    Robert
    Robert Chipperfield
    Red Gate
  • Options
    j.salmonj.salmon Posts: 44 Bronze 2
    Roger that - that would make to much sense :idea:
  • Options
    Greetings.

    I have been trying to get a command line comparison working and I am experiencing the same symptoms reported in this thread.

    I am running version 6.1.1.308 and when using the most basic of sdc files, it starts comparing and never comes back. My suspicion is that it is comparing everything.

    While it was running, I did sp_who2 on the source and destination servers and see the app running - oddly it was using much more CPU Time than Disk IO. I also see the command line process running on my workstation consuming plenty of local memory and CPU resources.

    I didn't get it to work until I added "/include:table:\[table1]".

    I thought this was fixed in 6.1.x. Is it possible that it wasn't, or that I have an old dll or something?

    Output:
    C:\Program Files\Red Gate\SQL Data Compare 6>sqldatacompare /project:"C:\UserDat
    aSynch\test2.sdc" /Include:Table:\[Table1]
    Red Gate SQL Data Compare Command Line Utility V6.1.1.308
    ==============================================================================
    Copyright c Red Gate Software Ltd 1999-2006

    Serial Number:XXX-XXX-XXXXXX-E448

    Registering databases
    Mapping
    Comparing databases
    Finished

    Summary Information
    ===============================================================================
    DB1 = Server1.DB1
    DB2 = Server2.DB2

    Object type Name Records DB1 DB2
    Table [dbo].[Table1] 1 <> <>
    ~ jeff ~
  • Options
    Hi Jeff,

    If you could contact support@red-gate.com, they should be able to provide you with a private release which should solve this problem.

    Hope that helps,
    Robert
    Robert Chipperfield
    Red Gate
Sign In or Register to comment.