Weird error (Msg 8107) on SQL Server 2008 R2 for SET IDENTITY_INSERT

Question

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);

I'm getting this error:

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

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

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.

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

What I want to know is:

1. Why does the second SET IDENTITY_INSERT statement fail?
2. Why does the error message even reference the unrelated [TABLE A]?
3. What can I do to fix it?

The scenario:

• I'm running this code one line at a time!
• I'm using SQL Server 2008 R2
• The code works just fine in the "dev" and "test" servers, but FAILS on the production server.
• The 'Compatibility Level' of the production server is 100. It's Level 80 for the "dev" and "test" servers.
• Their are no inter-dependencies between the tables in question.
• I am not the DBA. I can only run the code on "dev". The DBA must run the code on "Test" and "Prod".
• The DBA is as stumped as I am.

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]

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. :)

Answer 1

From here it say :

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 : It's failing because there is already a table in the database with IDENTITY_INSERT on.

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

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

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