Help and Support
 

powered byLive Search

PRB: Catastrophic Error Occurs Referencing ADO Recordset

Retired KB ArticleThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
View products that this article applies to.
Article ID:187942
Last Review:March 2, 2005
Revision:1.1
This article was previously published under Q187942
On This Page

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.

Back to the top

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.

Back to the top

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.

Back to the top

STATUS

This behavior is by design.

Back to the top

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)

Back to the top

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

Back to the top

Keywords: 
kbdatabase kbprb kbprovider KB187942

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by E-mail, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.