HOW TO: Use the SQLOLEDB "Preserve on Commit" and "Preserve on Abort" Dynamic Properties

This step-by-step article demonstrates how to implement the Preserve on Commit and Preserve on Abort dynamic properties of the Microsoft OLE DB Provider for SQL Server. You can use these properties to modify the default handling of cursors (ActiveX Data Objects [ADO] Recordset objects) when your code uses ADO transactions.

Description of the Technique

By default, Microsoft SQL Server closes an open server cursor when a transaction is committed or rolled back. This default behavior can be modified on the SQL Server side by using the SET CURSOR_CLOSE_ON_COMMIT statement.

The OLE DB Provider for SQL Server (SQLOLEDB) has the same default behavior: in an ADO application, SQLOLEDB closes a server-side cursor after an ADO transaction is committed or rolled back. However, the corresponding ADO Recordset is not destroyed but becomes unusable. If you try to call any property or method of the Recordset after you commit or roll back the transaction in which the Recordset was opened, you receive the following error message: To avoid this error message, you can use one of the following methods:

• Use a client-side cursor. The ADO Client Cursor Engine does not close cursors when you commit or roll back a transaction.
• Use the Requery method of the ADO Recordset to repopulate the server-side cursor.
• Use the dynamic SQLOLEDB properties, Preserve on Commit and Preserve on Abort, as described in this article.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Visual Studio Basic 6.0
• Available instance of Microsoft SQL Server 7.0 or Microsoft SQL Server 2000

This article assumes that you are familiar with the following topics:

• Visual Basic 6.0 development
• ADO data access

Steps to Build the Sample

1. Create a new Visual Basic 6.0 Standard EXE project, and then set a reference to Microsoft ActiveX Data Objects (ADO).
2. Drag a CommandButton control from the toolbox to the default form, Form1.
3. Add the following code in the Click event procedure of the CommandButton control:

       Dim cn As ADODB.Connection
       Dim rs As ADODB.Recordset
       Dim p As ADODB.Property
       Set cn = New ADODB.Connection
       cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
           "Initial Catalog=Northwind;User ID=<user>;Password=<password>"
       Set rs = New ADODB.Recordset
       With rs
           .CursorLocation = adUseServer
           .CursorType = adOpenDynamic
           .LockType = adLockPessimistic
           .Source = "SELECT * FROM Categories"
           Set .ActiveConnection = cn
           .Properties("Preserve on Commit") = True
       End With
       cn.BeginTrans
       rs.Open
       rs.MoveLast
       rs.Fields("Description").Value = "Just testing"
       rs.Update
       cn.CommitTrans
       rs.MoveFirst
       rs.Close
       cn.Close
   					

4. Modify the SQL Server connection string as necessary.
5. Run the project. Notice that you can still use the Recordset (in this case, by executing a MoveFirst method) after you commit the transaction.
6. Comment out the following line:

           .Properties("Preserve on Commit") = True
   					

7. Run the project again. Notice that you receive the "object is in a zombie state" error message on the "rs.MoveFirst" line because you cannot use the Recordset after you commit the transaction.

Troubleshooting

You must set the Preserve on Commit and Preserve on Abort properties:

• After you create the Recordset. -and-
  
  
• After you associate the Recordset with the open SQLOLEDB connection. -and-
  
  
• Before you open the Recordset.

If you try to set these properties before you associate the Recordset with the connection, you receive the following error message: If you try to set these properties after you open the Recordset, you receive the following error message: After you associate the Recordset with the open SQLOLEDB connection by using the ActiveConnection property of the Recordset, do not reference the connection again in the Open method of the Recordset.

For additional information about these properties, see the following Microsoft Knowledge Base article and MSDN Web sites: You can also find information about the Preserve on Commit and Preserve on Abort properties in SQL Server Books Online.