SQL Server 2008 R2出现SET IDENTITY_INSERT时的奇怪错误（Msg 8107）

Question

我试图运行这段代码：

SET IDENTITY_INSERT [dbo].[FIN_REP_REPORT_LOG] ON
SET IDENTITY_INSERT [dbo].[REPORT_LOG_SEQ] ON
INSERT INTO [dbo].[REPORT_LOG_SEQ] ([sqVal]) VALUES (1000000);

我遇到了这个错误： 为了方便，我们将FIN_REP_REPORT_LOG称为“表A”，将REPORT_LOG_SEQ称为“表B”。

在REPORT_LOG_SEQ表上的第二条IDENTITY_INSERT语句后，错误信息甚至与此无关！

SET IDENTITY_INSERT [TABLE B] ON
--Msg 8107, Level 16, State 1, Line 1
--IDENTITY_INSERT is already ON for table [TABLE A]. 
--Cannot perform SET operation for table [TABLE B].

INSERT INTO语句在[TABLE B]上失败了！

INSERT INTO [TABLE B] ([sqVal])
VALUES (1000000)
--Msg 544, Level 16, State 1, Line 1
--Cannot insert explicit value for identity column in table [TABLE B] when IDENTITY_INSERT is set to OFF.

[TABLE B]上的SET操作没有执行，因此INSERT操作失败了。

我想知道：

1. 为什么第二个SET IDENTITY_INSERT语句会失败？
2. 为什么错误消息会提到无关的[TABLE A]？
3. 我该怎么办才能解决这个问题？

场景描述：

• 我一次只执行一行代码！
• 我正在使用SQL Server 2008 R2。
• 该代码在“dev”和“test”服务器上都正常工作，但在生产服务器上失败了。
• 生产服务器的“兼容性级别”是100，“dev”和“test”服务器的级别为80。
• 涉及的表之间没有相互依赖。
• 我不是DBA。我只能在“dev”上运行代码。DBA必须在“Test”和“Prod”上运行代码。
• DBA像我一样被难住了。

FYI： FIN_REP_REPORT_LOG表（TABLE A）的定义：

CREATE TABLE [dbo].[FIN_REP_REPORT_LOG](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [REPORT_NAME] [nvarchar](50) NOT NULL,
    [EMPLOYEE_NUMBER] [nvarchar](10) NULL,
    [PARAMS] [nvarchar](1000) NULL,
    [START_DATE] [datetime2](0) NOT NULL,
    [END_DATE] [datetime2](0) NULL
) ON [PRIMARY]

报告日志SEQ（表B）的表定义如下：

CREATE TABLE [dbo].[REPORT_LOG_SEQ](
    [sqVal] [bigint] IDENTITY(1000000,1) NOT NULL
) ON [PRIMARY]

任何帮助（即使是线索）都将不胜感激。 :)

- user1058946

---

我认为你只能一次为一个表设置身份插入。看起来你正在尝试同时对两个表执行此操作。 - jazza1000

你已经为表A开启了IDENTITY_INSERT，现在你要操作表B，只需关闭表A的IDENTITY_INSERT并运行表B即可。 - mohan111

可能就是这样了。在开发和测试中，我们先处理一个表，然后再处理另一个表。 - user1058946

Answer 1

从这里可以得知：

在数据库中，IDENTITY_INSERT只能同时对一个表进行设置。在启用另一个表之前，必须将其关闭。

1：失败的原因是数据库中已经有一个启用了IDENTITY_INSERT的表。

2：错误状态指出哪个表已经启用了IDENTITY_INSERT。

3：看起来这段代码在开发环境中根本没有运行过。尝试在SSMS的新查询编辑器中运行下面的代码，看看是否会出现错误（替换表名）。

SET IDENTITY_INSERT [dbo].[Your tableName] ON
SET IDENTITY_INSERT [dbo].[Your tableName2] ON

我相信你会遇到错误。这应该能够澄清环境之间的区别。在测试后不要忘记关闭它，以避免在调试时出现麻烦。

SET IDENTITY_INSERT [dbo].[Your tableName] OFF

从那里开始，您将需要重构代码，一次只处理一个带有IDENTITY_INSERT的表。

- AXMIM

---

这在 MS SQL Server 2017 中仍然适用吗？ - WestCoastProjects