从标记为复制的 SQL 表中删除行
我错误地删除了合并复制中使用的 MS SQL 2000 表中的所有行（该表位于发布者上）。 然后，我使用 DTS 操作从备份数据库检索行并重新填充表，从而使问题变得更加复杂。
这产生了以下问题： 删除操作在客户端上将行标记为删除，但 DTS 操作绕过复制触发器，因此导入的行不会在订阅者上标记为插入。 实际上，订阅者会丢失数据，尽管数据位于发布者上。
所以我想“不用担心”，我将再次删除这些行，然后通过插入语句正确添加它们，然后将它们标记为插入订阅者。
这是我的问题： 我无法删除 DTSed 行，因为我收到“无法在具有唯一索引 'uc1MSmerge_tombstone' 的对象 'MSmerge_tombstone' 中插入重复的键行。” 错误。 我想做的是以某种方式从表中删除行，绕过合并复制触发器。 这可能吗？ 我不想删除并重做复制，因为订阅者有 50 多个 Windows 移动设备。
编辑：我尝试过截断表命令。 这会出现以下错误“无法截断表 xxxx，因为它是为复制而发布的”
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"
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您是否尝试过截断表格？
Have you tried truncating the table?
如果您需要插入的行具有相同的 ID，您可能必须截断表并将 ID 字段重置回 0。 如果没有，只需截断就可以了。
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.
您还可以考虑暂时删除唯一索引并在完成后将其添加回来。
You also could look into temporarily dropping the unique index and adding it back when you're done.
查看 sp_mergedummyupdate
Look into sp_mergedummyupdate
创建第二个表是一种选择吗？ 您可以创建第二个表，用所需的数据填充它，添加约束/索引，然后删除第一个表并重命名第二个表。 这应该为您提供带有正确键的数据...并且它应该全部包含允许滴入复制的 SQL 语句。 它只是可能不是最好的性能......并且肯定会带来一些风险。
我还没有在复制环境中亲自尝试过......但它至少值得尝试。
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.
感谢您的提示...我最终找到了解决方案：
我从表中删除了合并删除触发器
删除了 DTS 行
重新创建了合并删除触发器
使用插入语句正确添加我的行。
我有点担心摆弄合并触发器，但每件事似乎都工作正常。
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.