PRB: Error Creating More Than One Recordset in a Transaction

ID: Q180843

The information in this article applies to:

SYMPTOMS

When you use the OLE DB Provider for ODBC and 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.


CAUSE

This is caused by a limitation of the ODBC driver. If an ODBC driver does not support multiple active recordsets on the same connection, the OLE DB Provider for ODBC attempts to work around this limitation by creating a second connection. Because a manual transaction is being performed and transactions are at a connection level, the error occurs.

The SQL Server ODBC driver uses a special cursor when the cursor is forward-only, read-only, and the ODBC rowset size is one. The cursor is called a "firehose" cursor because it is the fastest way to retrieve the data. Unfortunately, a side affect of the cursor is that it only permits one active recordset per connection.


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


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

Additional query words: 


Keywords          : kbADO150 kbADO200 
Version           : WINDOWS:1.5
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 3, 1999