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(
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?