Cannot insert explicit value because IDENTITY_INSERT is OFF, but cannot SET IDENTITY_INSERT to ON because it is already ON

Question

I have a table in database Foo named Bar, that has a column named ID, which is the primary key, and this database is living on the development SQL Server.

I'm trying to copy data from our production server into the development server so I can play with said data, so I execute the following:

set IDENTITY_INSERT Foo.dbo.Bar ON
insert into Foo.dbo.Bar
(
   ID
   ,Something
   ,Else
   ,Is
   ,Going
   ,Horribly
   ,Wrong
   ,With
   ,SQL
)
select 
  ID
  ,Something
  ,Else
  ,Is
  ,Going
  ,Horribly
  ,Wrong
  ,With
  ,SQL
from Production.Foo.dbo.Bar

set IDENTITY_INSERT Foo.dbo.Bar OFF

And I get the error

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'Foo.dbo.Bar'. Cannot perform SET operation for table 'Foo.dbo.Bar'.

Hmm..okay, so IDENTITY_INSERT is turned on for the table. So I removed the SET IDENTITY_INSERT Foo.dbo.Bar ON from the top of my query, and then execute it, and I get this error:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Bar' when IDENTITY_INSERT is set to OFF.

I can execute SET IDENTITY_INSERT Foo.dbo.Bar OFF all day long, but if I try to turn it ON, then SQL Server 2012 says that IDENTITY_INSERT is already turned on.

Giesser

Is it possible it was turned on from a different session? – Choosy

Also, check for any triggers on this table. – Ultramarine

Is your session changing databases? Is it possible that it's ON for a table of the same name in another database? – Ultramarine

I cannot recreate this error. If IDENTITY_INSERT is on for a table I can continually set it to on with no error, similarly if it is off, I can set it to off, the only time I can't set it to ON is if it is there is another table in the same session. As you can see in this fiddle the first 3 batches work fine turning off and on again multiple times, it is only the 4 batch that fails. – Bluebonnet

I cannot recreate this error either. Either 1) this is an obscure SQL Server bug (unlikely), or 2) it's related to some distributed transaction context which I cannot test right now (not quite as unlikely), or 3) you've misread the actual names/situation somehow. – Ultramarine

I managed to solve my problem by executing my query in a new new query window. Thanks @AaronBertrand for putting me on the right track. – Giesser

Double-check the table name in the error matches the table name in the message. Gets me every time that it can be referring to a different table because only one table can have identity_insert on at a time. – Velure

Answer 1

Funny - i just had the exact same problem with 3 of 4 tables in my brand new ss2012 database.

So i rewrote the table create script to bracket my inserts with 'set identity on' and 'set identity off' and it works. It seems like ss only wants to handle one table's identity at a time ie you cant set a new table identity on until all the others have been turned off.

something like this:

create table Employers
(
id int PRIMARY KEY IDENTITY ,
companyid nvarchar(50) not null,
companyName nvarchar(80) not null,
address nvarchar(80),
Phone nvarchar(10),
);

SET IDENTITY_INSERT employers ON;

insert into Employers(id,companyid,companyName,address,Phone)
Values
(...),
(...)

SET IDENTITY_INSERT employers OFF;

create table customers
(
...

Vanzant

Answer 2

I actually found a different solution to this. This is actually different from Msg 8101, specific to table identity being not set to ON.

To resolve the problem, you need to drop the schema and tables of the same names in the master database.

I have a template script that creates a database and tables and inserts data into those tables, something like:

USE master
GO
CREATE DATABASE [mydb] ON  PRIMARY 
( NAME = N'mydb', FILENAME = N'C:\SQLDATA\mydb.mdf' , SIZE = 5072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'mydb_log', FILENAME = N'C:\SQLDATA\mydb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [mydb]
GO

CREATE SCHEMA [myschema] AUTHORIZATION [dbo]
GO

CREATE TABLE [myschema].[mytable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SOMETHING] [int] NOT NULL,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
)
GO

SET IDENTITY_INSERT [myschema].[mytable] ON 
GO

INSERT [myschema].[mytable] ([ID], [SOMETHING]) VALUES (1,2)
GO

SET IDENTITY_INSERT [myschema].[mytable] OFF
GO

The problem was that during the database creation, the statement failed due to the "device activation" error (permissions to the folder where the MDF file will be created), or because of the database file size. The script continued but created the schemas and tables in the master database instead. After I realised the error, I stopped the script and attempted to run it again with the create issues fixed.

Then I got the error.

Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'master.myschema.mytable'. Cannot perform SET operation for table 'myschema.mytable'.
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'mytable' when IDENTITY_INSERT is   set to OFF.

I'm no SQL Server expert, but I think it looks up the schema in the master database first, and because this schema is found and in the current session IDENTITY is already on, it produces this error message.

After dropping the schemas and tables incorrectly created in the master database, my script ran successfully.

Cancroid

Somehow at some point my script created the tables in the master instead of in my database. Deleting them as you pointed out did the job. Thanks!!! You'll notice that the question was: Insert for Foo.dbo... Is ON. In my case it was saying: Insert for master.dbo... Is ON. – Esme

Answer 3

I had the exact same error. I was using linq-to-sql and the table and dbml file was initially generated without an identity column on the primary key.
I later updated the primary key to an Identity column but forgot to update the dbml file.

Cannot insert explicit value for identity column in table 'BLAH' when IDENTITY_INSERT is set to OFF

The fix was to update the dbml file funnily enough...

Leoraleos