Problem with IDENTITY_INSERT when setting a Column Identity

[mikemcginty]

After importing Access tables into SQL the primary keys and identities are not set so I wrote a VB6 program to execute script to create a table with primary keys and identities, copy data then drop the original table (as per Enterprise Manager script).

As can be seen below the first table converts ok but the second table produces an error (at 21/09/2004 2:44:08 PM) claiming that IDENTITY_INSERT is set to OFF.

Why does this error occur when I have previously set IDENTITY_INSERT to ON? The script also ran ok from Enterprise manager.

21/09/2004 2:44:06 PM (SQL Execute) SET QUOTED_IDENTIFIER ON
21/09/2004 2:44:06 PM (SQL Execute) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
21/09/2004 2:44:06 PM (SQL Execute) SET ARITHABORT ON
21/09/2004 2:44:06 PM (SQL Execute) SET NUMERIC_ROUNDABORT OFF
21/09/2004 2:44:06 PM (SQL Execute) SET CONCAT_NULL_YIELDS_NULL ON
21/09/2004 2:44:06 PM (SQL Execute) SET ANSI_NULLS ON
21/09/2004 2:44:06 PM (SQL Execute) SET ANSI_PADDING ON
21/09/2004 2:44:06 PM (SQL Execute) SET ANSI_WARNINGS ON
21/09/2004 2:44:06 PM (SQL Execute) CREATE TABLE Tmp_tblQADM_Catalogue
(
CatalogueID int NOT NULL IDENTITY (1, 1),
TableName nvarchar(60) NOT NULL,
fCertificateID int NOT NULL,
Editor nvarchar(25) NULL
) ON [PRIMARY]

21/09/2004 2:44:07 PM (SQL Execute) SET IDENTITY_INSERT Tmp_tblQADM_Catalogue ON

21/09/2004 2:44:07 PM (SQL Execute) IF EXISTS(SELECT * FROM tblQADM_Catalogue)
EXEC('INSERT INTO Tmp_tblQADM_Catalogue (CatalogueID, TableName, fCertificateID, Editor)
SELECT CatalogueID, TableName, fCertificateID, Editor FROM tblQADM_Catalogue TABLOCKX')

21/09/2004 2:44:07 PM (SQL Execute) SET IDENTITY_INSERT Tmp_tblQADM_Catalogue OFF
21/09/2004 2:44:07 PM (SQL Execute) DROP TABLE tblQADM_Catalogue
21/09/2004 2:44:07 PM (SQL Execute) EXECUTE sp_rename N'Tmp_tblQADM_Catalogue', N'tblQADM_Catalogue', 'OBJECT'
21/09/2004 2:44:07 PM (SQL Execute) ALTER TABLE tblQADM_Catalogue ADD CONSTRAINT
PK_tblQADM_Catalogue PRIMARY KEY CLUSTERED
(
CatalogueID
) ON [PRIMARY]
21/09/2004 2:44:07 PM (ALTER TABLE)
21/09/2004 2:44:07 PM (ALTER TABLE) A0101a Client - In
21/09/2004 2:44:08 PM (SQL Execute) SET QUOTED_IDENTIFIER ON
21/09/2004 2:44:08 PM (SQL Execute) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
21/09/2004 2:44:08 PM (SQL Execute) SET ARITHABORT ON
21/09/2004 2:44:08 PM (SQL Execute) SET NUMERIC_ROUNDABORT OFF
21/09/2004 2:44:08 PM (SQL Execute) SET CONCAT_NULL_YIELDS_NULL ON
21/09/2004 2:44:08 PM (SQL Execute) SET ANSI_NULLS ON
21/09/2004 2:44:08 PM (SQL Execute) SET ANSI_PADDING ON
21/09/2004 2:44:08 PM (SQL Execute) SET ANSI_WARNINGS ON
21/09/2004 2:44:08 PM (SQL Execute) CREATE TABLE Tmp_UtblQADMA0101aClientIn
(
CertificateID int NOT NULL IDENTITY (1, 1),
fCategoryID int NOT NULL,
Certificate nvarchar(255) NOT NULL,
Subject nvarchar(255) NOT NULL,
OwnerName nvarchar(50) NOT NULL,
fDocProvider int NULL,
DocNumber nchar(100) NOT NULL
) ON [PRIMARY]

21/09/2004 2:44:08 PM (SQL Execute) SET IDENTITY_INSERT Tmp_UtblQADMA0101aClientIn ON

21/09/2004 2:44:08 PM (SQL Execute) IF EXISTS(SELECT * FROM UtblQADMA0101aClientIn)
EXEC('INSERT INTO Tmp_UtblQADMA0101aClientIn (CertificateID, fCategoryID, Certificate, Subject, OwnerName, fDocProvider, DocNumber)
SELECT CertificateID, fCategoryID, Certificate, Subject, OwnerName, fDocProvider, DocNumber FROM UtblQADMA0101aClientIn TABLOCKX')

21/09/2004 2:44:08 PM (VBMTech - Execute SQL) Error -2147217900 (Cannot insert explicit value for identity column in table 'Tmp_UtblQADMA0101aClientIn' when IDENTITY_INSERT is set to OFF.)

21/09/2004 2:44:08 PM (SQL Execute) SET IDENTITY_INSERT Tmp_UtblQADMA0101aClientIn OFF

Thanks in advance

Mike


When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[mikemcginty]

Just in case anyone is interested I read somewhere that IDENTITY_INSERT is only valid for one table per session (I presume this is an SQL session)

I resolved this by executing the separate chunks of sql script between and including BEGIN TRANSACTION and COMMIT but removing any GO statements.

In the above problem I was executing individual lines of script leaving out the BEGIN TRANSACTION and COMMIT statements.

Mike

When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!