Data compare seems to compare tables out of order

[mwatkins@xsinc.com]

Hello,

I have been working on something for some time now and struggling. We have our database deployments fully automated with Schema and Data comparisons but every once in a while we run into an issue where we get an UPDATE error on a Forign Key constraint. The error is as follows:

Exception of type System.Data.SqlClient.SqlException was thrown. Details:

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Menu_Item_Page". The conflict occurred in database "DCP_Membership", table "dbo.Page", column 'Page_Key'.

This ocurrs becuase there was a record added to the Page table that the Menu_Item table references, however that Insert has not been executed yet. When debugging, I see that the order of tables that we get back from Red-Gate is out of order in how these records need to be updated.

Is there any way around this or am I missing something simple? Seems like this would be an issue for many here on the forum but I have not found anything regarding it.

Thanks,

Mike

[james.billings]

Firstly, you should ensure that you've ticked the option "Disable Foreign Keys" - this will get data compare to disable all FK's before starting the process.
If you still find you encounter the error you may also have triggers that are fired on one of the inserts that tries to update something when it shouldn't, and again there are options to disable triggers within Data Compare.

[mwatkins@xsinc.com]

Hi James,

I am using the SDK for the sync process and from what the documentation says, the SqlOptions.DisableKeys is set. Is there another setting I should use in the SDK?

Thanks,

Mike