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;
}
}