Cannot insert duplicate key row in object

Question

Cannot insert duplicate key row in object

Sharon Kumar

Ten Centuries

Points: 1193
More actions
August 30, 2011 at 7:16 am

#243108

Hi all,
In Transactional Replication I'm facing this issue "Cannot insert duplicate key row in object 'TABLE' with unique Index 'Index Name'".
Actually I'm Not inserting a new row, I'm just updated a column on publisher database.
I looked some suggestions in Internet. They are suggesting delete a record in that subscriber database.
After Deleted a record in the subscriber database, Replicated transaction is not a insert a records, Its was updating transaction.
How can it will update a record in the subscriber if the Record is not available.
If I'm doing any wrong method could you please suggest me Steps to Troubleshoot this issue.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you update a column and that makes it violate the unique constraint (or unique index), that's the same as inserting a duplicate row. You have to either change what you're updating to something unique, or you have to get rid of or modify the existing row so it doesn't have the same value as you're trying to update.

One way to do that is delete the existing row.

Or update it.

Or change your mind about updating the row you're trying to update.

It all depends on what you're trying to do.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sharon Kumar Ten Centuries Points: 1193 More actions · Answer 2

Thanks For you information...

Can you please help on this too..

----------------------------------------------------------

I was skip a transaction in Transactional Replication. I followed these to skip a transactions which is mentioned in the msdn site.

http://blogs.msdn.com/b/chrissk/archive/2009/09/08/how-to-skip-a-transaction-in-sql-2005-2008-transactional-replication.aspx?CommentPosted=true#commentmessage

After a skipping a transaction successfully & my replication is also working fine.

But there are few transactions which are waiting to be applied.

After Checking more details on undistributed commands, I found these are related the transactions which are skipped previously.

what will happen after skipping transactions, those will a in undistributed transactions history,

is there any thing else to delete those records ????

GSquared SSC Guru Points: 260824 More actions · Answer 3

Can't help on that. I've never tried to skip a transaction in replication.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

If done correctly, those skipped commands should be clean up as part of the distribution clean up job as they no longer fit into the transaction sequence.