PRB: Error Creating More Than One Recordset in a Transaction

• ActiveX Data Objects (ADO), version 1.5

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"

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 code below 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

Keywords          : adoengdb adoiis adovb adovc adovj
Technology        : odbc
Version           : WINDOWS:1.5
Platform          : WINDOWS
Issue type        : kbprb