插入复制表失败 - 身份范围检查

本文介绍了插入复制表失败 - 身份范围检查的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！

问题描述

我正在尝试将几千行插入到跨两台服务器复制的数据库中的表中.无论是发布者还是订阅者，我都收到了同样的错误:

I'm trying to insert a few thousand rows into a table in a database that is replicated across two servers. From either the publisher or the subscriber, I get the same error:

Msg 548, Level 16, State 2, Line 1
The insert failed. It conflicted with an identity range check constraint in database 'XXX', replicated table 'dbo.NODE_ATTRIB_RSLT', column 'ID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
The statement has been terminated.

检查表上的约束，在我看来，我应该能够在遇到问题之前一次插入至少 1000 行.但是，当我尝试仅插入几十行时，我遇到了同样的错误！

Checking the constraint on the table, it seems to me like I should be able to insert at least 1000 rows at a time before running into issues. However, I get the same error when trying to insert just a few tens of rows!

这是我尝试插入数据的方式:

Here's how I'm trying to insert data:

insert into NODE_ATTRIB_RSLT 
([NODE_ID]
      ,[ATTRIB_ID]
      ,[STATE_ID]
      ,[PLAN_REVISION_ID]
      ,[TIMESTAMP]
      ,[VALUE]
      ,[VALUE_TEXT]
      ,[LAST_MODIFIED]) 
SELECT [NODE_ID]
          ,[ATTRIB_ID]
          ,[STATE_ID]
          ,[PLAN_REVISION_ID]
          ,[TIMESTAMP]
          ,[VALUE]
          ,[VALUE_TEXT]
          ,[LAST_MODIFIED]   FROM [NODE_ATTRIB_RSLT_TEMP]

PK 列是一个自动生成的身份，称为 ID.为了尝试一次插入更少的行，我在选择的末尾添加了一个 WHERE 子句，如下所示:

The PK column is an autogenerated identity called ID. To try to insert fewer rows at a time I've added a WHERE clause at the end of the select like so:

WHERE ID >= 1000 and ID <1100

但无济于事.

在发布服务器上运行 sp_adjustpublisheridentityrange 会成功执行但没有任何效果.

Running sp_adjustpublisheridentityrange on the Publisher executes successfully but has no effect.

如何使用插入来解决此问题?如何在保持复制运行的同时将身份范围限制的范围修改为更合理的级别?

How can I fix this problem with inserts? How can I modify the ranges of the indentity range contraints to a more reasonable level while leaving the replication running?

在发布服务器上运行 sp_adjustpublisheridentityrange 存储过程.

服务器上的新查询窗口
选择正确的数据库
执行 sp_adjustpublisheridentityrange @table_name = 'node_attrib_rslt'

从订阅者那里，强制同步服务器.

在订阅服务器上，展开复制 --> 本地订阅
右键点击特定订阅并选择查看订阅状态.
在出现的对话框中，按下监视器按钮.
在出现的复制监视器"窗口中，展开左侧窗格中的特定发布者.
点击订阅进行编辑.
在右侧窗格中，右键单击订阅状态并选择开始同步.
状态应在其执行操作时更新为正在同步".
完成后，点击警告和代理"标签.我在下部窗格中列出了一个快照代理".右键单击该代理并启动它.运行一段时间后，服务器上的属性更改应该已迁移到客户端.

也许:在表中插入一些测试行.

我最近不得不再次执行此任务，并且在我将一堆虚拟数据插入表中以耗尽默认约束之前，表上的约束不会更新.然后我重新同步了服务器，并将约束更新为新值.

Maybe: insert some test rows into the table.

I've had to do this task again recently, and the constraint on the table would not update until I inserted a bunch of dummy data into the table so as to exhaust the default constraint. Then I resyncronised the servers, and the constraint was updated to the new value.

之后，检查身份约束发现我终于有一个 20K ID 范围可以插入到发布者和订阅者上.

After that, checking the identity constraint revealed that I finally had a 20K ID range to insert with on both the Publisher and the Subcriber.

这篇关于插入复制表失败 - 身份范围检查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！