Microsoft KB Archive/187942

= PRB: Catastrophic Error Occurs Referencing ADO Recordset =

Article ID: 187942

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft OLE DB Provider for SQL Server 7.01

-



This article was previously published under Q187942



SYMPTOMS
Any operation following a rollback or a commit transaction on a recordset opened as a serverside cursor, triggers one the following errors, depending on the provider and operating system:

Run-time error '-2147418113' Catastrophic failure

-or-

Run-time error '-2147418113' Unexpected failure

Using ADO 2.6 and later, the error is Run-time error '-2147418113(8000ffff)': ITransaction::Commit or ITransaction::Abort was called, and the object is in a zombie state.



CAUSE
Preserving cursors, or in other words, not closing them, is not the SQL Server or ANSI SQL default. The OLE DB specification does not specify a default value for these properties because, this behavior can change from provider to provider.

The Cursor Engine, however, does preserve cursors.



RESOLUTION
Use adUseClient or set the following RecordSet properties to preserve the cursor:

rs.Properties("Preserve On Commit") = True rs.Properties("Preserve On Abort") = True

There are three requirements to have these two properties, or any other preset properties, take effect on a recordset. The three requirements are:


 * The properties need to be set prior to opening the recordset.
 * Use the Open method to open the recordset. The Connection and Command Execute method opens a default Recordset, with all properties preset.
 * The OLE DB provider must support preserving cursors. The OLE DB Provider for SQL Server supports preserving cursors on Commit and Abort.

If you use adOpenForwardOnly as a cursor type and adLockReadOnly as a lock type, setting "Preserve on Commit" to True will not have any effect. You will need to call Recordset.Requery after you commit the transaction if any further work needs to be done on the Recordset.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
 Start Visual Basic. Add a reference to the Microsoft ActiveX Data Objects Library.  Add the following code to the default form in the project: Dim cn As New ADODB.Connection Dim rst As New ADODB.Recordset

cn.Open "provider=SQLOLEDB;data source= ;initial " _ & "catalog=pubs;user id= ;password= " ' error handling for non-existent Test1 table On Error Resume Next cn.Execute "drop table Test1" On Error GoTo 0 cn.Execute "create table Test1(id int primary key, num int)"

For i = 1 To 10 cn.Execute "insert into Test1 values(" & i & ", " & i & ")" Next i

Set rst.ActiveConnection = cn  'Set these properties to True to prevent error. 'rst.Properties("Preserve On Commit") = True 'rst.Properties("Preserve On Abort") = True

cn.BeginTrans rst.Open "select * from Test1",, adOpenStatic, adLockOptimistic Debug.Print rst(0) cn.RollbackTrans ' If the preserve properties are not set, the following fails Debug.Print rst(0) 

Keywords: kbprovider kbdatabase kbprb KB187942

-

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

© Microsoft Corporation. All rights reserved.