SQL Server 2008 R2 上 SET IDENTITY_INSERT 的奇怪错误(消息 8107) [英] Weird error (Msg 8107) on SQL Server 2008 R2 for SET IDENTITY_INSERT

问题描述

我正在尝试运行这段代码:

I'm trying to run this piece of code:

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".

For reference: I'll refer to FIN_REP_REPORT_LOG as 'TABLE A', and REPORT_LOG_SEQ as 'TABLE B'.

REPORT_LOG_SEQ 表中第 2 个 IDENTITY_INSERT 语句之后的错误消息甚至不相关！

The error message after the 2nd IDENTITY_INSERT statement on the REPORT_LOG_SEQ table doesn't even relate!

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] 失败！

The INSERT INTO statement then fails for [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 操作失败.

The SET operation on [TABLE B] doesn't happen, so the INSERT operation fails.

我想知道的是:

1. 为什么第二个 SET IDENTITY_INSERT 语句失败?
2. 为什么错误消息甚至引用了不相关的 [TABLE A]?
3. 我该怎么做才能解决这个问题?

场景:

• 我一次运行一行代码！
• 我使用的是 SQL Server 2008 R2
• 代码在开发"和测试"服务器中运行良好，但失败在生产服务器上.
• 生产服务器的兼容级别"为 100.它的级别开发"和测试"服务器为 80.
• 它们在相关表之间没有相互依赖关系.
• 我不是 DBA.我只能在dev"上运行代码.DBA 必须运行Test"和Prod"上的代码.
• DBA 和我一样困惑.

仅供参考:FIN_REP_REPORT_LOG 的表定义(表 A):

FYI: Table definition for 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]

REPORT_LOG_SEQ (TABLE B) 的表定义:

The table definition for REPORT_LOG_SEQ (TABLE B):

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

任何帮助(甚至线索)将不胜感激.:)

Any help (even clues) will be appreciated. :)

推荐答案

来自 这里 它说:

IDENTITY_INSERT 一次只能为数据库中的一个表设置为 on.它必须先关闭，然后才能为不同的表再次打开.

IDENTITY_INSERT can only be set to on for one table in a database at a time. It must be turned off, before it can be turned on again for a different table.

1 : 失败，因为数据库中已经有一个 IDENTITY_INSERT 打开的表.

1 : It's failing because there is already a table in the database with IDENTITY_INSERT on.

2 : 表的 IDENTITY_INSERT 已经打开的错误状态.

2 : The error state which table has IDENTITY_INSERT already on.

3:似乎这段代码根本没有在开发环境中运行.尝试在 SSMS 的新查询编辑器中运行下面的代码，看看是否出现错误.(替换表名)

3 : It seem that this code wasn't run at all on dev environment. Try to just run the code bellow in a new query editor in SSMS and see if you get the error. (replace table name)

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

我相信你会得到这个错误.这应该澄清环境之间的差异.测试后不要忘记运行关闭，以免调试时出现问题.

I believe you will get the error. This should clarify the difference between environment. Don't forget to run the turn off after the test to avoid trouble while debuging.

SET IDENTITY_INSERT [dbo].[Your tableName] OFF

从那里开始，您将不得不重构代码以在启用 IDENTITY_INSERT 的情况下只有一张表.

From there you will have to refactor the code to have only one table at the time with IDENTITY_INSERT on.

这篇关于SQL Server 2008 R2 上 SET IDENTITY_INSERT 的奇怪错误(消息 8107)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！