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:
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.
<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>
Additional query words: Kbdsupport, kbgrpAspDB, kbmdac250bug
Keywords: kbprb KB250910
