Microsoft KB Archive/250910

From BetaArchive Wiki
Knowledge Base

PRB: Error Does Not Occur When Setting IDENTITY_INSERT ON For More Than One Table

Article ID: 250910

Article Last Modified on 6/24/2004


  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 7.0 Service Pack 1

This article was previously published under Q250910


If you use the SQL Server OLE DB provider (SQLOLEDB) and a Server side cursor, SQL Server 7.0 should generate an error when you set the IDENTITY_INSERT property to ON for more than one table per session, but it does not. You may still use SQLOLEDB with a Server side cursor as long as you are aware of the potential erroneous behavior. The error message that should occur is:

Microsoft OLE DB Provider for SQL Server (0x80040E14) IDENTITY_INSERT is already ON for table 'pubs.dbo.table1'. Cannot perform SET operation for table 'jobs'.

SQL Server Books On-Line states the following:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERTON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.


Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.


Use the SQLOLEDB with a client side cursor if you need to trap for the error condition. Also, the Microsoft OLEDB Provider for ODBC (MSDASQL) correctly generates the error condition regardless of the cursor location.


The following steps outline how to reproduce the problem by running code from an Active Server Pages (ASP). However, this problem is not specific to ASP.

To reproduce this problem paste the following code in a new ASP file (you can use NotePad). Place the ASP page in your \InetPub\WWWRoot directory and execute with your browser. Note that this sample is based on SQL Server 7.0 and uses the Pubs database. You may need to change the connection information for your environment.

<%@ Language=VBScript %>
Dim adoConnection
Dim adoRecordset
Dim strSQL

Set adoConnection = CreateObject("adodb.Connection")
'Uncomment these lines to use Microsoft OLEDB for ODBC.
'adoConnection.ConnectionString = "Provider=MSDASQL;" & _
'  "Driver={SQL Server};UID=<username>;" & _
'  "Database=Pubs;Server=(local)"
adoConnection.ConnectionString = "Provider=SQLOLEDB;" & _
   "User ID=<username>;Initial Catalog=Pubs;" & _
   "Data Source=(local)"

adoConnection.Execute "if exists (select * from sysobjects where id = object_id('table1')" & _
   "and objectproperty(id, N'IsUserTable') = 1) drop table table1"
adoConnection.Execute "create table table1 (col1 int identity(1,1) primary key)"
adoConnection.Execute "SET IDENTITY_INSERT Table1 ON"
adoConnection.Execute "Insert into Table1 (col1) values (16)"

Set adoRecordset = CreateObject("adodb.Recordset")
Set adoRecordset.ActiveConnection = adoConnection
adoRecordset.CursorLocation = 2 '2=adUseServer; 3=adUseClient
adoRecordset.CursorType = 2 'adOpenDynamic
adoRecordset.LockType = 3 'adLockOptimistic
strSQL = "select col1 from Table1"
adoRecordset.Open strSQL

adoConnection.Execute "SET IDENTITY_INSERT jobs ON"

Response.Write adoConnection.ConnectionString & "<br><br>"
Response.Write "No Error Message!  In this situation an error is expected." & "<br>"

Set adoRecordset=nothing
Set adoConnection=nothing

Additional query words: Kbdsupport, kbgrpAspDB, kbmdac250bug

Keywords: kbprb KB250910