PRB: Error Creating More Than One Recordset in a Transaction

ID: Q180843

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2

SYMPTOMS

When you try to create more than one recordset in the middle of a transaction, the following error occurs:

Cannot create a new connection because in manual transaction mode

The HRESULT is 0x80004005.

Alternatively, you might see the following, more informative error message:

Runtime error '-2147467259(80004005)':
You can't have multiple recordsets with this cursortype (in a transaction.) Either change the cursor type, commit the transaction, or close one of the recordsets.

CAUSE

SQL Server can only open one ForwardOnly cursor at a time on a connection, because SQL Server can only process one active statement at a time per connection.

When you try to open more than one ForwardOnly ADO recordset at a time on a single Connection, only the first ADO recordset is actually opened on the Connection object. New, separate connections are created for subsequent ForwardOnly cursors.

A transaction is on a single connection. When you attempt to open more than one ForwardOnly recordset within a single transaction, ADO attempts to open more than one ForwardOnly recordset on the connection of the transaction. An error occurs because SQL Server only allows one ForwardOnly recordset on a single connection. Because the error is within a manual transaction, you might see the error above. Microsoft Data Access Objects 2.1 Service Pack 2 contains more informative error messages. For that reason, you may see the more informative, second error above.

RESOLUTION

Use a different cursor type or close the first recordset before opening another on the same connection/transaction.

For example, if you use the following code the error will not occur:


Dim cnn1 As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

strCnn = "DSN=Springhill;uid=sa;pwd=;database=pubs"
cnn1.Open strCnn
cnn1.BeginTrans
rs1.Open "Select * from authors", cnn1, adOpenStatic, adLockOptimistic
rs2.Open "Select * from publishers", cnn1, adOpenStatic,

adLockOptimistic

cnn1.CommitTrans
cnn1.Close

NOTE: If you refer to either recordset after the transaction has been committed or aborted, you see the following error:

Run-time error '-2147418113': Catastrophic failure

You must either use and then close the recordsets within the transaction or preserve the cursors. The OLE DB Provider for ODBC does not allow preserving cursors, so you must close the recordsets within the transaction. The OLE DB Provider for SQL Server does allow preserving cursors. See the REFERENCES section of this article for more inromation.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

The following Visual Basic code causes the error to occur:


Dim cnn1 As ADODB.Connection
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strCnn As String

Set cnn1 = New ADODB.Connection
strCnn = "DSN=SQLServer;uid=sa;pwd=;database=pubs"

cnn1.Open strCnn
cnn1.BeginTrans
Set rs1 = cnn1.Execute("Select * from authors")
Set rs2 = cnn1.Execute("Select * from publishers")

cnn1.CommitTrans

cnn1.Close
End Sub

REFERENCES

For additional information on the 'Catastrophic failure' message in this context, click the article number below to view the article in the Microsoft Knowledge Base:

Q187942 PRB: Catastrophic Error Occurs Referencing ADO Recordset

For additional information on ForwardOnly recordsets and ADO, click the article number below to view the article in the Microsoft Knowledge Base:

Q235282 INFO:Opening Multiple ForwardOnly ADO Recordsets with SQL Server Spawns Additional Connections

Additional query words:

Keywords : kbADO150 kbADO200 kbDatabase kbSQLServ kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbprb