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


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 7.0 Service Pack 1
This article was previously published under Q250910

SYMPTOMS

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 ServerT returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

CAUSE

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

WORKAROUND

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.

MORE INFORMATION

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. 

<HTML>
<BODY>
<%@ 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.Open

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

adoRecordset.Close
adoConnection.Close
Set adoRecordset=nothing
Set adoConnection=nothing
%>
</BODY>
</HTML>
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb KB250910
©2004 Microsoft Corporation. All rights reserved.