March 26, 2002 at 11:00 am
Hi, the following error shows in the Transactional replication for one table "Cannot insert duplicate key row in object 'XXX_TableName' with unique index 'XXX_TableName_U1'." I ran the query to check any duplicate key but didn't find any. Any idea what might be the cause of this message?
Thanks a lot,
Cathy
March 26, 2002 at 11:05 am
That message is usually right on target. Had it happen to me a couple times when someone in IS inserted a row on the subscriber by mistake, realized it, then inserted on the publisher. This article might help:
http://www.sqlservercentral.com/columnists/awarren/dupekeyinserted_1.asp
Andy
March 26, 2002 at 2:43 pm
Thanks for all these solutions. The problem in my system is a little different, because there's NO duplicate key even in the publication side. I suspect it's a sql70 replication bug. Has anyone getting the same problems before?
Thanks a lot,
Cathy
March 26, 2002 at 3:20 pm
I've not seen a bug about this, have you checked MSDN? How/where are you checking for the duplicate key?
Andy
March 26, 2002 at 4:15 pm
The query I used to check the duplicate key is >> select count(*)
from tbl1
group by col1, col2, col3
having coount(*) > 1
I ran this query against the publication table and has 0 row returned.
Thanks
Cathy
March 26, 2002 at 4:56 pm
That's a good start. But potentially you have the situation I mentioned earlier, the key somehow already exists on the subscriber and you're trying to insert it again - that creates the duplicate key error. To check for that you'd have to look through the pending repl transactions, looking for an insert that would cause the problem. Do you have a unique index defined on the publisher on the three cols you mentioned?
To fix you either have to remove the transaction that is causing the problem or send over a new snapshot. Better to find the problem if you can and have time so you can take steps to prevent it.
Andy
September 12, 2002 at 8:57 am
My co-worker started this topic and I am picking it up again because it has ocurred again. Usually when I re-initialize replication, the problem goes away. It occurs every once in a while in my test environment and occassionally, I have had to actually recreate the publisher database to get rid of the duplicate error, Cannot insert duplicate key row in object.... When I recreate the publisher database as a new database and load the data from the previous db before creating replication again, the replication error goes away even after heavy activity following the snapshot. Here is more background.
I am getting the following error on 3 different tables all using transactional replication without immediate updating
subscribers: Cannot insert duplicate key row in object 'S_ORDER_ITEM' with unique index 'S_ORDER_ITEM_U1'.
I found a reported bug for SQL 2000 (BUG #: 357212 (SHILOH_BUGS) ) with this error message for replication using immediate udating subscribers that was fixed in sp3, and I wonder if this bug also exists for version 7 without immediate updating subscribers.
My source sql server database is version 7.0 sp1 with a patch required for Siebel (Microsoft SQL Server 7.00 - 7.00.703 (Intel X86) ) and my destination sql server
database is version 7.0 sp2 (Microsoft SQL Server 7.00 - 7.00.842 (Intel X86)).
No one would be able to insert transactions directly to the subscription database that are identical to the transactions in the publication.
It seems like the distribution agent is receiving the insert statement twice or applying the insert statement twice to the subscriber sometimes but not all of the time. I have found that the sp causing the error, CALL sp_MSins_..., must have already been applied to the subscription because I can find that transaction in my subscription database already yet the distribution agent is attempting to call the sp again. I would like to be able to confirm this by viewing a history of the stored procedure calls against the subscription. My next step is to turn on Profiler to prove that the sp is being called more than once for the same transaction. Of course, when I had profiler on this morning the problem didn't occur. Not until I stopped Profiler, did the problem occur again.
Last night, I shrunk the log files of the publication and subscription and hope that might work. Just in case it doesn't, I wanted to see if my description of the problem sparks any ideas.
Any help is greatly appreciated! Thanks!
Gwen
September 12, 2002 at 11:06 am
I think continuing to Profile makes sense, worst that happens is you disprove your theory!
Andy
September 16, 2002 at 11:10 am
Do you have triggers on the publisher and the subscriber that might be putting duplicate rows in a table? You would need to make the trigger on the subscriber NOT FOR REPLICATION to fix this or alternatively, remove the article for the target table of the trigger from replication.
September 19, 2002 at 12:43 pm
You should be able to start up profiler and then restart the distribution agent to recreate the error. I would suggest turning on SP statements in Profiler
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply