Red Gate forums :: View topic - Table/row order in SDK gen. script not based on dependency
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

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

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
dshah



Joined: 26 Sep 2012
Posts: 2

PostPosted: Fri Sep 06, 2013 7:10 am    Post subject: Table/row order in SDK gen. script not based on dependency Reply with quote

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!
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6678

PostPosted: Tue Sep 10, 2013 9:09 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group