February 9, 2006 at 9:19 am
I have a replication running to three remote sites and although 2 work fine the 3rd creates conflict errors. The error is “can’t insert row into table B - record not found in related table A”. Both table A and B are correctly populated in the publishing database.
I am guessing that the order in which the tables and replicated is wrong – it tries to insert into table B before table A and can’t. When the replication is finished table A is fully populated with all the results that would allow the inserts into table B, but obviously not at the time of trying to!
How does the replication decide the order in which tables are replicated? This error only occurs on the one table and not for any of the many others with similar foreign keys.
How can I define the order to avoid this happening?
I am using SQLServer 2000.
Any ideas would be greatly appreciated.
Thanks
Cayley
February 9, 2006 at 12:27 pm
If I were you I would double check the data filtering you have in place for that subscriber. Typically, SQL Server replication does not get the ordering wrong (at least in my 5 years of experience with with SQL CE).
The fact that it successfully works on two other subscribers suggests that the 'ordering' is indeed correct.
Often, if you have filters in place, you will find that data being published may not be what you had hoped for. Sounds to me like this might be what is happening for you.
HTH
February 9, 2006 at 1:15 pm
Thanks for your help. I agree - it's probably not the replication randomly ordering things wrong, but I've must have pressed something different somewhere which has caused the problem. Trouble is I can't work out what.
I have no filters set up for rows or columns at the publisher. Where could they be for an individual subscriber?
Could differences in collation between the different servers make a difference? Although the collation is the same for all the databases.
Thanks
Cayley
February 9, 2006 at 2:08 pm
If collation is the same on all databases, then that shuold not be an issue.
Filtering is set up in the actual publication. If you right click the publication and select properties, there is a Filters Tab. This shows how the data is filtered - and needs to be set up by you, but sounds to me like this is not the case for you.
Sorry I can't be of more help.
February 9, 2006 at 3:19 pm
Cayley,
You have stated that "The error is “can’t insert row into table B - record not found in related table A”."
Are you sure that the record that Replication is trying to INSERT into Table B on the Subscription Database actually does have a parent record in Table A on the Subscription Database?
Also which Replication Model are you using? Transactional or Merge?
February 10, 2006 at 5:23 am
The replication is needs to insert into both tables A and B. On the the other two replication databases the insert of parent into A appears to happen first as the insert of child into B work fine.
However on the third troublesome database the insert into B must be happening after A. The parent record hasn't yet been inserted into A therefore the trigger produces the error. The parent record is then inserted afterwards into A.
I am using a merge replication.
Unfortunately on the next replication attempt this causes the records to be deleted from the publisher, which then deletes from all the other subscribers as well!
Thanks for your help
Cayley
February 10, 2006 at 7:41 am
Check your ForeignKeys. There is a "not for replication" option which will prevent this error from occuring.
Replication does batch inserts, so Parent-Child order doesn't always occur.
February 10, 2006 at 8:02 am
Its acutally a trigger causing the errors rather than just the FK relationship. Certainly there is nothing in the relationships tab for the table properties so I can't turn it off. Is there a similar option anywhere for turning triggers off for replication?
I still can't work out why this would only happen to one table in particular and not any of the others, nor why it only happens in one of three subscribers!
Thanks for your help.
Cayley
February 10, 2006 at 8:07 am
Yes, the triggers have the same "not for replication" option. Just alter your trigger:
FOR INSERT, UPDATE NOT FOR REPLICATION
February 10, 2006 at 11:25 am
Thankyou very much for your help. I have altered the triggers and will see how it goes.
Regards
Cayley
February 10, 2006 at 10:54 pm
Another ooption for turning off the triggers is to use the settings for each article in the replication property pages
Right click the publication and select properties
Select the articles tab
Select the ... button for the table(s) with the triggers
On the first tab you will find a tick box for replicating triggers - switch this off.
February 15, 2006 at 1:34 am
Hi
The Not For Replication is certainly a solution in this case.
As far as ordering goes, when a batch of commands are replicated they are put in an order. They use nicknames which means a primary record will have a lower nickname/number than a foreign key record. So in a batch the primary keys are applied first then the fk records. One weakness here is that there is no way of ensuring all the relevant records have been captured in the batch. So another option is to increase your batch size.
.....I think that's right. We had a similar problem.
HTH
Regards..Graeme
February 15, 2006 at 5:00 am
"Another ooption for turning off the triggers is to use the settings for each article in the replication property pages"
This option is for what schema to generate/apply for replication. It won't turn off triggers, it will just not generate the trigger ddl for application to the subscriber(s) if the "initialize schema and data" option is selected.
Cayley would still want the triggers to fire on the subscriber because they are part database. However, Cayley may not want the triggers to fire on data that is being replicated to the subscriber, or back to the publisher as they would have already fired on the initial DML statement.
February 15, 2006 at 5:16 am
Thanks for your help everyone.
I have added the 'not for replication' to the triggers (at both publisher and subscriber) and as yet have been unable to deliberately create conflicts. Maybe this is because I have only tried with a few records, or maybe it's worked!!
The increasing batch size sounds like a good idea. All three of my merge replication agents are set to the default agent profile (so still a bit odd why it's only one that creates these conflicts). Should I change this to the high volume option, or create a new profile with BcpBatchSize increased?
The truth is that on a normal basis the volume of data added by the users isn't very big at all. It's only every now and then that I add a alot of rows, and even then it's not huge.
Yes, definately need the triggers on the subscribers, and I am still a bit nervous about turning them off for replication. Is it just me but is replication just like a big mystery black box?
Regards
Cayley
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply