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