Microsoft KB Archive/301953

= 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

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.194
 * Microsoft SQL Server 7.0 Standard Edition

-



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 &quot;drain abort&quot; 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.



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.



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



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.



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.



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 &quot;drain abort&quot; 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 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

 * 1) 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.
 * 2) 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
 * 1) Start the DTC service on the local computer and on the SQL Server computer being connected to.
 * 2) Run the code until the &quot;return 0&quot; statement at the end, but do not execute it yet.
 * 3) In SQL Profiler, note that two sessions have been started, and that a DTC transaction has been propagated to both.
 * 4) Return to the Visual C++ IDE and execute the &quot;return 0&quot; statement. Note that there is a delay executing this command.
 * 5) Return to SQL Profiler and note that a &quot;drain abort&quot; event has been listed in the Event Sub Class column and the transaction has been aborted.

Sample code

 * 1) define UNICODE
 * 2) define _UNICODE
 * 3) include 
 * 4) define DBINITCONSTANTS
 * 5) define INITGUID
 * 6) include 
 * 7) include 
 * 8) include 
 * 9) include 
 * 10) include 
 * 11) include 

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] = &quot;select shipperid, companyname, phone from shippers&quot;;

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&quot;northwind&quot;); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, L&quot;YourServer&quot;); dbinit.AddProperty(DBPROP_AUTH_USERID, L&quot;sa&quot;); dbinit.AddProperty(DBPROP_AUTH_PASSWORD, L&quot;&quot;);

hr = connection.OpenWithServiceComponents(&quot;SQLOLEDB&quot;, &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; }

Additional query words: dtc distributed transaction coordinator enlist enlistment sql server provider sqloledb hang timeout drain abort session release active pending com+ mts

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp2fix kbmdac260fix kbmdac260sp2fix kbhotfixserver KB301953

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.