Deleting Rows from a SQL Table marked for Replication

Question

I erroneously delete all the rows from a MS SQL 2000 table that is used in merge replication (the table is on the publisher). I then compounded the issue by using a DTS operation to retrieve the rows from a backup database and repopulate the table.

This has created the following issue: The delete operation marked the rows for deletion on the clients but the DTS operation bypasses the replication triggers so the imported rows are not marked for insertion on the subscribers. In effect the subscribers lose the data although it is on the publisher.

So I thought "no worries" I will just delete the rows again and then add them correctly via an insert statement and they will then be marked for insertion on the subscribers.

This is my problem: I cannot delete the DTSed rows because I get a "Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'." error. What I would like to do is somehow delete the rows from the table bypassing the merge replication trigger. Is this possible? I don't want to remove and redo the replication because the subscribers are 50+ windows mobile devices.

Edit: I have tried the Truncate Table command. This gives the following error "Cannot truncate table xxxx because it is published for replication"

Answer 1

Have you tried truncating the table?

Answer 2

You may have to truncate the table and reset the ID field back to 0 if you need the inserted rows to have the same ID. If not, just truncate and it should be fine.

Answer 3

You also could look into temporarily dropping the unique index and adding it back when you're done.

Answer 4

Would creating a second table be an option? You could create a second table, populate it with the needed data, add the constraints/indexes, then drop the first table and rename your second table. This should give you the data with the right keys...and it should all consist of SQL statements that are allowed to trickle down the replication. It just isn't probably the best on performance...and definitely would impose some risk.

I haven't tried this first hand in a replicated environment...but it may be at least worth trying out.

Answer 5

Look into sp_mergedummyupdate

Answer 6

Thanks for the tips...I eventually found a solution:

I deleted the merge delete trigger from the table
Deleted the DTSed rows
Recreated the merge delete trigger
Added my rows correctly using an insert statement.

I was a little worried bout fiddling with the merge triggers but every thing appears to be working correctly.