PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction

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 "References" 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 "Symptoms" 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 <username> and password =<strong 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
#include <iostream.h>
#include <atldbcli.h>
#include <comdef.h>

void myErrHandler();

int main(int argc, char* argv[])
{
CDataSource DataSource;
CSession Session;
CDBPropSet propset;
CCommand<CDynamicAccessor,CRowset> command1;
CCommand<CNoAccessor, CNoRowset> command2;

HRESULT hr;


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

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

}

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

Session.StartTransaction();

hr = command1.Open(Session,"SELECT * FROM pubs..authors");
if (!SUCCEEDED(hr))
{
cout << "Could not open rowset associated with command1" << 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, "INSERT INTO pubs..TAB1 VALUES(100)", 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 << "Error Message:" << (_bstr_t)(myErrStr) << endl;
}
}

References

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
271128 PRB: Implicit Connections Created by the SQL Server OLE DB Provider (SQLOLEDB) Are Not Pooled
Eigenschaften

Artikelnummer: 272358 – Letzte Überarbeitung: 04.08.2008 – Revision: 1

Feedback