PRB: Catastrophic Error Occurs Referencing ADO Recordset

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
• Microsoft OLE DB Provider for SQL Server, version 7.01

SYMPTOMS

Any operation following a rollback or a commit transaction on a recordset opened as a serverside cursor, triggers the following error:

Run-time error '-2147418113' Catastrophic failure

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 

• 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.

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) 

Additional query words:

Keywords : kbADO200 kbDatabase kbOLEDB kbProvider kbSQLServ kbVBp kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:2.0,2.1 SP2,7.01
Platform : WINDOWS
Issue type : kbprb