MSmerge_tombstone
-
OK, this is a wierd one - at least for me.
I have a db set up for merge replication. The subscribers are all handhelds using SQL CE.
My issue is this:
I have a child table with three fields. An ID field (uniqueidentifier rowguid and primary key), a data field (varchar) and the ParentID (uniqueidentifier).
However, this table has dupicate records for some reason. And I mean identical - even the ID column which is set as Primary Key and rowguid! (Up to 8 identical records.)
I have no idea how this occurred, but now I'm trying to delete the records. I'm okay with deleting all the dupilcates, I don't need to retain any.
But I keep getting this error:
Cannot insert duplicate key row in object 'MSmerge_tombstone' with unique index 'uc1MSMerge_tombstone'.
Because the one field is rowguid I can't seem to delete because it will insert duplicate key into the MSmerge_tombstone table - from what I gather.
Any thots?
Five Stones IT Consulting and Development -
well this is a new one for me because if you have PK constraint violated seems like a serious flaw in the engine (which I don't believe such thing can happen at this state of the art in dbms
) That leads me to believe that you should have some corruption there.Have you run DBCC CHECKDB on that database?
* Noel -
Well, I fell pretty stupid here....
Sorry, the ID field is NOT a Primary Key. It was intended to be a primary key, but did not get created that way and when I posted my first message I was looking at the creation script given to me and not the table itself. Sorry.
However, it is a rowguid column. Does that not affect things in pretty much the same manner? Is this not supposed to still be a unique field?
Anyway, I'm still stuck with trying to delete these records. I can't delete them because the MSmerge_tombstone error mentioned in the first post.
Any thots on how to delete them?
Five Stones IT Consulting and Development -
OK,
To delete those rows just disable the triggers on that table (hopefully you have only the merge triggers in there
you should verify it! and if that was the case then :alter table <YourTableName> DISABLE TRIGGER ALL
--execute your deletes here
alter table <YourTableName> ENABLE TRIGGER ALL
hth
* Noel -
Fantastic.
I will give that a whirl in the next couple of days (the server is in a production environment that I will not have access to for a couple of days.)
But that sounds like a very resonable approach and should work.
Thanx so much.
Five Stones IT Consulting and Development -
Just one bummer about the solution above:
SQL Server won't let you alter the table because it is being published for replication. So you'll have to drop the table from the publication, then alter it, but if you're dropping it from replication, it will drop those triggers anyway.
Just my $.02
(I found this because I'm having the same problem, except there aren't duplicate rows in the table that's being deleted from!)
Rick Todd
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply