PRB: Catastrophic Error Occurs Referencing ADO Recordset (187942)

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.

MORE INFORMATION

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=<server>;initial " _
   & "catalog=pubs;user id=<user id>;password=<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)