Microsoft KB Archive/250910

= 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

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * 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 Server™ 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.   <%@ 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= ;" & _ ' "Database=Pubs;Server=(local)" adoConnection.ConnectionString = "Provider=SQLOLEDB;" & _ "User ID= ;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 & " " Response.Write "No Error Message! In this situation an error is expected." & " "

adoRecordset.Close adoConnection.Close Set adoRecordset=nothing Set adoConnection=nothing %>  

Additional query words: Kbdsupport, kbgrpAspDB, kbmdac250bug

Keywords: kbprb KB250910

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.