PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction (272358)

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