Data compare seems to compare tables out of order

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

Data compare seems to compare tables out of order

Postby mwatkins@xsinc.com » Wed Jan 26, 2011 5:19 pm

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
mwatkins@xsinc.com
 
Posts: 3
Joined: Mon Nov 01, 2010 6:57 pm

Postby james.billings » Thu Jan 27, 2011 6:56 pm

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.
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby mwatkins@xsinc.com » Mon Feb 07, 2011 8:04 pm

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
mwatkins@xsinc.com
 
Posts: 3
Joined: Mon Nov 01, 2010 6:57 pm


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests