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

问题描述

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

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?

推荐答案

我想我已经弄清楚问题出在哪里了.

I think I worked out what the problem was.

查看复制表的属性，它的标准默认标识范围是发布者的 10000 和订阅者的 1000.

Looking at the properties for the replicated table, it had the standard default identity range of 10000 for the Publisher and 1000 for the Subcriber.

但是，检查实际表上的身份约束(使用 SP_HELPCONSTRAINT 'node_attrib_rslt')发现两台服务器上只有 1000 个 ID 的池.即使我限制了要插入的行数，这也会导致批量插入失败 - 我猜 SQL Server 在运行 INSERT INTO 时检查约束时甚至没有那么远.

However, checking the identity constraint on the actual table (using SP_HELPCONSTRAINT 'node_attrib_rslt') revealed that there was only a pool of 1000 IDs on both servers. This made the bulk insert fail even when I restricted the number of rows to insert - I'm guessing SQL Server doesn't even get that far when it checks the constraint when running an INSERT INTO.

为了修复它，我必须做几件事:

To fix it I had to do several things:

1. 在发布服务器上，展开复制 --> 本地发布
2. 右键单击特定订阅并选择属性.
3. 选择文章"页面.
4. 突出显示适当的表格.
5. 点击文章属性按钮"，然后选择设置突出显示表格文章的属性".
6. 在文章属性窗口中，查找身份范围管理选项.
7. 更改适当的值.
8. 在对话窗口中按 OK 和 OK.

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

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

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

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

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

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

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.

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