Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
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
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.
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.
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:
290211For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/290211/ )How to obtain the latest SQL Server 2000 service pack
(http://support.microsoft.com/kb/300635/ )How to obtain the latest MDAC 2.6 service pack
HotfixThe 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
You can work around this problem in the following ways:
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.
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.
Note that the symptoms of this problem are very similar to those outlined in the following article in the Microsoft Knowledge Base:
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
297266It 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.
(http://support.microsoft.com/kb/297266/ )FIX: SQLOLEDB: Incorrect transaction enlistment causes hang and drain abort entry
Steps to reproduce the behavior
Article ID: 301953 - Last Review: September 26, 2005 - Revision: 4.2