Select the product you need help with

Unenlisting from active transaction using SQLOLEDB provider of MDAC 2.6 release against SQL Server 7.0 causes hang and drain abort entry

Article ID: 301953 - View products that this article applies to.

This article was previously published under Q301953

SYMPTOMS

When using the SQL Server native provider (SQLOLEDB) against a SQL Server 7.0 database, and when running in a transactional context such as MTS or COM+, directly or indirectly releasing an OLE DB session object that is enlisted in a distributed transaction can cause the client application to hang until the transaction timeout occurs.

If SQL Profiler is used to monitor the client activity against the server, a "drain abort" entry will be displayed in the Event Sub Class column.

This occurs when using the SQL Server 2000/MDAC 2.6 release of SQLOLEDB (version 2000.80.194); it does not occur with earlier versions. This problem also does not occur when connecting to a SQL Server 2000 database.

Back to the top | Give Feedback

CAUSE

In SQL Server 2000, it is possible for an OLE DB session that is enlisted in a distributed transaction to unenlist from that transaction without calling Commit or Rollback. Doing so transfers ownership of the transaction to a SQL Server worker thread, and the OLE DB session is free to do other work.

This is not possible in SQL Server 7.0, which does not have the ability to transfer ownership of a transaction, and will not permit a session to unenlist without first committing or rolling back the transaction.

Because SQL Server 2000 supports transaction unenlistment, new code in the 2000.80.194 version of the SQL Server provider checks to see if the session is enlisted in any active transactions before disconnecting. If so, the provider unenlists from the transaction just prior to disconnecting, but does not check to see which version of SQL Server it is connected to. If the provider is connected to a SQL Server 7.0 server, this causes the client to hang waiting for a response from the server which never occurs.

Back to the top | Give Feedback

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 2000, the latest service pack for MDAC 2.6, or the hotfix referenced below. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211

(http://support.microsoft.com/kb/290211/ )

How to obtain the latest SQL Server 2000 service pack

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

300635

(http://support.microsoft.com/kb/300635/ )

How to obtain the latest MDAC 2.6 service pack

Hotfix

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel. \

   Date          Version         Size            File name
   -----------------------------------------------------------
   11-JUN-2001   2000.80.301.0   491,584 bytes   Sqloledb.dll
   11-JUN-2001   2000.80.301.0    61,440 bytes   Sqloledb.rll
   8-JAN-2001                      1,652 bytes   Eula.txt

Back to the top | Give Feedback

WORKAROUND

You can work around this problem in the following ways:

Use a SQL Server 2000 database server.
Revert back to an earlier release of the SQL Server provider, such as the MDAC 2.5 SP2 version. Doing so may cause the loss of some features, such as the ability to use new SQL Server 2000 datatypes.

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 2000 Service Pack 2 and MDAC 2.6 Service Pack 2.

Back to the top | Give Feedback

MORE INFORMATION

The sample output below is from a SQL Profiler trace taken while executing transactional commands and then releasing the OLE DB session objects. Note that the sessions (Connection IDs/SPIDs) are each enlisted in the transaction, the statements are executed, and a "drain abort" entry then appears in the Event Sub Class column.

Event Class		Event Sub Class		Text					Connection ID	SPID
+DTCTransaction		idle			cfd6f325-650c-11d5-9375-00105a997a14		423		9
+DTCTransaction		enlisting			cfd6f327-650c-11d5-9375-00105a997a14		423		9
+DTCTransaction		active			cfd6f327-650c-11d5-9375-00105a997a14		423		9
 DTCTransaction		propagate transaction						423		9
 Connect											429		10
 ExistingConnection									429		10
 DTCTransaction		get address							429		10
+DTCTransaction		idle			cfd6f327-650c-11d5-9375-00105a997a14		429		10
 DTCTransaction		propagate transaction						429		10
 Connect											430		11
 ExistingConnection									430		11
 DTCTransaction		get address							430		11
+DTCTransaction		idle			cfd6f327-650c-11d5-9375-00105a997a14		430		11
 DTCTransaction		propagate transaction						430		11
+SQL:BatchCompleted				SELECT au_id, au_lname, au_fname FROM pubs..authors	423	9
+RPC:Completed					sp_cursoropen @P1 output, N' SELECT au_id, title_id FROM pubs..titleauthor', ...
+RPC:Completed					sp_cursorfetch 309149788, 32, 1, 1 		429		10
+RPC:Completed					sp_cursorclose 309149788 			429		10
+RPC:Completed					sp_cursoropen @P1 output, N' SELECT emp_id, fname, lname FROM pubs..employee',...
+RPC:Completed					sp_cursorfetch 310206556, 32, 1, 1 		430		11
+RPC:Completed					sp_cursorclose 310206556 			430		11
+DTCTransaction		drain abort		cfd6f327-650c-11d5-9375-00105a997a14		430		11
+DTCTransaction		aborting			cfd6f327-650c-11d5-9375-00105a997a14		
+DTCTransaction		idle			cfd6f327-650c-11d5-9375-00105a997a14		430		11
+DTCTransaction		propagate transaction	cfd6f327-650c-11d5-9375-00105a997a14		430		11

Note that the symptoms of this problem are very similar to those outlined in the following article in the Microsoft Knowledge Base:

297266

(http://support.microsoft.com/kb/297266/ )

FIX: SQLOLEDB: Incorrect transaction enlistment causes hang and drain abort entry

It is important to carefully investigate the symptoms and the SQL statments being executed to determine which problem is being encountered. Examination of a SQL Profiler trace is highly recommended. If there is any doubt about which hotfix should be used, use the hotfix discussed in this article because it also contains the fix for the issue described in Q297266.

Steps to reproduce the behavior

Copy the code below into a new Microsoft Visual C++ console application, add a Link reference to xolehlp.lib, and compile the code. Note that you may need to change the data source name, user ID, and password so that these values refer to valid values for your SQL Server.
Start the SQL Profiler and include the following events and columns:
Events
All items under the Sessions event
Trasactions -- DTC Transactions
TSQL -- SQL:StmtStarting
Columns
Event Class
Event Sub Class
Text
Transaction ID
Connection ID
SPID
Start the DTC service on the local computer and on the SQL Server computer being connected to.
Run the code until the "return 0" statement at the end, but do not execute it yet.
In SQL Profiler, note that two sessions have been started, and that a DTC transaction has been propagated to both.
Return to the Visual C++ IDE and execute the "return 0" statement. Note that there is a delay executing this command.
Return to SQL Profiler and note that a "drain abort" event has been listed in the Event Sub Class column and the transaction has been aborted.

Sample code

#define UNICODE
#define _UNICODE
#include <windows.h>
#define DBINITCONSTANTS
#define INITGUID
#include <stdio.h>
#include <stddef.h>
#include <tchar.h>
#include <atldbcli.h>
#include <txdtc.h>
#include <xolehlp.h>

class CShippers
{
public:
	long m_ShipperID;
	CHAR m_CompanyName[80];
	CHAR m_Phone[20];

	BEGIN_COLUMN_MAP(CShippers)
		COLUMN_ENTRY(1, m_ShipperID)
		COLUMN_ENTRY(2, m_CompanyName)
		COLUMN_ENTRY(3, m_Phone)
	END_COLUMN_MAP()

};


int main(int argc, char* argv[])
{
	
	USES_CONVERSION;
	HRESULT hr = NULL;

	CoInitialize(NULL);

	CDataSource connection;
	CSession session1, session2;
	CCommand<CAccessor<CShippers> > shippers1, shippers2;
	const char SQL[80] = "select shipperid, companyname, phone from shippers";

	ITransactionDispenser *pTransactionDispenser = NULL;
	ITransactionJoin * pITransactionJoin1;
	ITransactionJoin * pITransactionJoin2;
	ITransaction * pITransaction;
	
	hr = DtcGetTransactionManager(NULL, NULL,
			IID_ITransactionDispenser, 0, 0, NULL,
			(void**) &pTransactionDispenser);
	
	hr = pTransactionDispenser->BeginTransaction (
			NULL,		// Controlling IUnknown
			ISOLATIONLEVEL_READCOMMITTED,	// Isolation level
			0,				// Isolation Flags
			NULL,			// Reserved
			&pITransaction);	// Ptr to the Transaction Object



	CDBPropSet dbinit(DBPROPSET_DBINIT);
	dbinit.AddProperty(DBPROP_INIT_CATALOG, L"northwind");
	dbinit.AddProperty(DBPROP_INIT_DATASOURCE, L"YourServer");
	dbinit.AddProperty(DBPROP_AUTH_USERID, L"sa");
	dbinit.AddProperty(DBPROP_AUTH_PASSWORD, L"");


	hr = connection.OpenWithServiceComponents("SQLOLEDB", &dbinit);

	hr = session1.Open(connection);
	hr = session2.Open(connection);

	hr = session1.m_spOpenRowset->QueryInterface(IID_ITransactionJoin, (void**) &pITransactionJoin1);
	hr = session2.m_spOpenRowset->QueryInterface(IID_ITransactionJoin, (void**) &pITransactionJoin2);

	hr = pITransactionJoin1->JoinTransaction((IUnknown*) pITransaction, ISOLATIONLEVEL_READCOMMITTED, 0, NULL);
	hr = pITransactionJoin2->JoinTransaction((IUnknown*) pITransaction, ISOLATIONLEVEL_READCOMMITTED, 0, NULL);


	hr = shippers1.Open(session1, SQL, NULL, NULL, DBGUID_DBSQL, true);
	hr = shippers2.Open(session2, SQL, NULL, NULL, DBGUID_DBSQL, true);

	hr = shippers1.MoveFirst();
	hr = shippers2.MoveFirst();

	shippers1.Close();
	shippers2.Close();

	pITransactionJoin1->Release();
	pITransactionJoin2->Release();

	session1.Close();
	session2.Close();

	connection.Close();

//	hr = pITransaction->Commit( 0, XACTTC_SYNC_PHASEONE, 0 );
	pITransaction->Release();
	pTransactionDispenser->Release();

	
	return 0;
}

Back to the top | Give Feedback