April 26, 2010 at 2:50 pm
I have t-replication setup for inserts (only). I had some insert failures on the publisher on the
primary key and it seems sql server was trying to replicate the same, resulting in insert failures at the subscriber too. There is no identity key involved.
What could be the cause and a potential solution for this problem? I had to reinitialize and it worked, but I need a better solution and also need to reason why failed inserts were replicated in the first place
Thanks..
April 27, 2010 at 11:48 am
LogReader only replicates "committed" transactions.
IF you have a PK violation on the primary it *wont* replicate because the action is rolledback.
IF you see that the values were replicated somehow they were "successfully" entered on the primary!
* Noel
April 29, 2010 at 12:43 pm
The most likely cause is (because you only replicate inserts), a primary key value was updated or deleted on the primary, then the "free" key value was reused by an insert. Of course that value is still being used on your replicated copy, so you get a primary key error.
Solutions include replicating updates and deletes as well as inserts, or (what we do) replicating inserts using a custom stored procedure that TRYs the INSERT, then CATCHes duplicate keys and instead does an UPDATE to ensure the two sides agree. You still should at least replicate UPDATEs, since otherwise you could have primary key values in your primary that do not exist in your copy.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply