Microsoft KB Archive/272358

= PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction =

Article ID: 272358

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft OLE DB Provider for SQL Server 7.01
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.194

-



This article was previously published under Q272358



SYMPTOMS
With the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), when you try to run multiple commands in the same session within the scope of a transaction, you may receive the following error message:

Cannot create new connection because in manual or distributed transaction mode.

The error code (HRESULT) that is associated with this error message is E_FAIL or 0x80004005 or -2147467259.

This error message occurs only when all of the following conditions are met:
 * The transaction is active.
 * There is already an active command (pending resultset) and you are trying to run another command in the same session/connection.
 * The pending resultset is associated with forward-only and read-only cursor (also known as firehose or default resultset).
 * The DBPROP_MULTIPLECONNECTIONS property on the data source is set to VARIANT_TRUE.



CAUSE
The Microsoft OLE DB Provider for SQL Server does not allow more than one active connection within the scope of a transaction.



RESOLUTION
Use a server-side or client-side cursor other than the forward-only and read-only (often called a firehose) cursor.



MORE INFORMATION
Because transactions and locking are managed by SQL Server on a per-connection basis, SQLOLEDB does not permit more than one active connection within the scope of a transaction.

Following is an excerpt from a Microsoft Knowledge Base article (Q271128--see the &quot;References&quot; section):

Because the SQL Server OLE DB provider doesn't permit more than one set of results to be pending on a connection where the results are being returned by means of a forward-only, read-only (default-resultset) cursor, the provider needs to create an additional SQL Server connection to execute a second command on the connection. The provider will only do this implicitly if the Data Source property DBPROP_MULTIPLECONNECTIONS is set to VARIANT_TRUE.

Thus, running multiple commands in the same session, meeting the criteria mentioned in the &quot;Symptoms&quot; section of this article, causes SQLOLEDB to open a new connection. This results in the following error message when the transaction is active:

Cannot create new connection because in manual or distributed transaction mode.

The following code sample, which uses the Active Template Library (ATL) OLE DB consumer templates, demonstrates this behavior:

Note You must change User ID and password = to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database
 * 1) include 
 * 2) include 
 * 3) include 

void myErrHandler;

int main(int argc, char* argv[]) {   CDataSource                 DataSource; CSession                   Session; CDBPropSet                 propset; CCommand            command1; CCommand          command2;

HRESULT hr;

hr = CoInitialize(NULL); if (!SUCCEEDED(hr)) {       cout << &quot;Could not initialize COM&quot; << endl; return 0; }

hr = DataSource.OpenFromInitializationString(L&quot;Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=MSSQLServer01;&quot;                                                     L&quot;User ID= ;password= ;Initial Catalog=Pubs;&quot;); if (!SUCCEEDED(hr)) {       cout << &quot;Could not connect to SQL Server&quot; << endl; return 0;

}   hr = Session.Open(DataSource); if (!SUCCEEDED(hr)) {       cout << &quot;Could not create session&quot; << endl; return 0; }

Session.StartTransaction;

hr = command1.Open(Session,&quot;SELECT * FROM pubs..authors&quot;); if (!SUCCEEDED(hr)) {       cout << &quot;Could not open rowset associated with command1&quot; << endl; return 0; }

// At this point, the first resultset is still pending and you are trying to execute another command. // (Table TAB1 has a single INT type column)

hr=command2.Open(Session, &quot;INSERT INTO pubs..TAB1 VALUES(100)&quot;, NULL, NULL, DBGUID_DBSQL, false); if (FAILED(hr)) myErrHandler; command1.Close; command1.ReleaseCommand; command2.Close;

Session.Commit; Session.Close; DataSource.Close;

return 0; }

void myErrHandler {   CDBErrorInfo myErrorInfo; ULONG numRec = 0; BSTR myErrStr,mySource; ISQLErrorInfo *pISQLErrorInfo = NULL; LCID lcLocale = GetSystemDefaultLCID; myErrorInfo.GetErrorRecords(&numRec); if (numRec) {       myErrorInfo.GetAllErrorInfo(0,lcLocale,&myErrStr,&mySource); cout << &quot;Error Message:&quot; << (_bstr_t)(myErrStr) << endl; } }

