Table/row order in SDK gen. script not based on dependency

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Table/row order in SDK gen. script not based on dependency

Postby dshah » Fri Sep 06, 2013 7:10 am

Hi, I am using the v10.0.1.69 version of the DataCompare SDK dll and SQL Server 2005.

I generate a data script by comparing 2 databases. The generated script does not have the correct dependency order of tables/rows in some cases.

1. For a self referencing table (example: table SiteMap has PK SiteMapId and FK ParentSiteMapId (NULLABLE)), the order of insert rows generated by the script fails to account for dependency due to ParentSiteMapId.
2. I found another scenario where in spite of specifying the FK in the database, the generated script has an incorrect order of tables, I haven't found the pattern why this specific table fails yet.

My database has over 100 tables, and for the most part the order seems fine otherwise. I confirmed the similar results when generating the script through UI.

Note: I am trying to avoid dropping constraints in the beginning and re-adding them in the end, I am aware that solution would work fine.

I am using the following options in my code:
tableMappings.Options = new EngineDataCompareOptions(
MappingOptions.Default,
ComparisonOptions.ForceBinaryCollation | ComparisonOptions.Default,
SqlOptions.ForceCheck | SqlOptions.OutputCommentHeader);

Am I missing something obvious?
Is there a list of things to confirm so the generated script returns the correct order based on dependency between tables?
Can I reliably depend on the SDK to produce a script that honors dependency?

Thanks!
dshah
 
Posts: 2
Joined: Wed Sep 26, 2012 1:29 am

Postby Brian Donahue » Tue Sep 10, 2013 9:09 am

SQL Data Compare and the SDK generate insert/update/delete statements in batches for one table, then the next, and so on. If there are foreign key relationships, you can only use the "DisableKeys" option and that will script a drop and re-create of any necessary foreign keys whilst the update is running.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests