Microsoft KB Archive/321714

= HOW TO: Use the SQLOLEDB &quot;Preserve on Commit&quot; and &quot;Preserve on Abort&quot; Dynamic Properties =

Article ID: 321714

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q321714



IN THIS TASK
SUMMARY
 * Description of the Technique
 * Requirements
 * Steps to Build the Sample
 * Troubleshooting

REFERENCES



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

back to the top

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:

Run-time error '-2147418113 (8000ffff)':

ITransaction::Commit or ITransaction::Abort was called, and object is in a zombie state.

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.

back to the top

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

back to the top

Steps to Build the Sample
 Create a new Visual Basic 6.0 Standard EXE project, and then set a reference to Microsoft ActiveX Data Objects (ADO). Drag a CommandButton control from the toolbox to the default form, Form1.  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 &quot;Provider=SQLOLEDB;Data Source= ;&quot; & _ &quot;Initial Catalog=Northwind;User ID= ;Password= &quot; Set rs = New ADODB.Recordset With rs       .CursorLocation = adUseServer .CursorType = adOpenDynamic .LockType = adLockPessimistic .Source = &quot;SELECT * FROM Categories&quot; Set .ActiveConnection = cn       .Properties(&quot;Preserve on Commit&quot;) = True End With cn.BeginTrans rs.Open rs.MoveLast rs.Fields(&quot;Description&quot;).Value = &quot;Just testing&quot; rs.Update cn.CommitTrans rs.MoveFirst rs.Close cn.Close  Modify the SQL Server connection string as necessary. Run the project. Notice that you can still use the Recordset (in this case, by executing a MoveFirst method) after you commit the transaction.  Comment out the following line: .Properties(&quot;Preserve on Commit&quot;) = True  Run the project again. Notice that you receive the &quot;object is in a zombie state&quot; error message on the &quot;rs.MoveFirst&quot; line because you cannot use the Recordset after you commit the transaction.</li></ol>

back to the top

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:

Run-time error '3265':

Item cannot be found in the collection corresponding to the requested name or ordinal.

If you try to set these properties after you open the Recordset, you receive the following error message:

Run-time error '3705':

Operation is not allowed when the object is open.

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.

back to the top

<div class="references_section">