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

dshah

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!

Brian Donahue

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.