Questions on how to programmatically sync w/ where clause
j.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.
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
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
Red Gate
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.
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:
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
Red Gate
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?
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
Red Gate
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 <> <>
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
Red Gate