PRB: Catastrophic Error Occurs Referencing ADO Recordset

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

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.

More Information

Steps to Reproduce Behavior

  1. Start Visual Basic.
  2. Add a reference to the Microsoft ActiveX Data Objects Library.
  3. 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)
Propiedades

Id. de artículo: 187942 - Última revisión: 03/02/2005 - Revisión: 1

Comentarios