FIX: SQLOLEDB: Incorrect Transaction Enlistment Causes Hang and Drain Abort Entry
This article was previously published under Q297266
This article has been archived. It is offered "as is" and will no longer be updated.
When you use the native SQL Server OLE DB provider (Sqloledb.dll) in a transactional environment, the computer may stop responding (hang) for the length of the transaction timeout setting.
This occurs under the following conditions:
- You are using the SQL Server provider in a transactional environment, such as MTS/COM+.
- The SQL Server database version is 7.0.
- You are using ADO client-side cursors.
- The cursor engine determines that more meta data is needed from the provider.
This occurs with version of the SQL Server provider that ships with SQL Server 2000 and MDAC 2.6. It does not occur with earlier versions. This behavior also does not occur when you use SQL Server 2000.
The ADO client cursor engine requests extra meta data from a provider if it determines that some meta data is missing. When the SQL Server provider is queried for this meta data, an undercover connection is spawned to collect this information from the server.
Normally, these unexposed connections should not be enlisted in any current transactions. However, a missing function parameter in the provider causes these meta data connections to incorrectly enlist in the transaction, which causes the application to stop responding until the transaction timeout occurs.
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
300635 INFO: How to Obtain the Latest MDAC 2.6 Service Pack
HotfixThe version of this English has the file attributes (or later) 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 ------------------------------------------------------------ 04/11/2001 2000.80.380.0 483,412 bytes Sqloledb.dll 04/11/2001 2000.80.380.0 61,440 bytes Sqloledb.rll
WORKAROUNDTo work around this problem, use one or more of the following recommendations. Note that these recommendations may not work in all circumstances. The symptoms and behavior may depend on the combination of SQL statements, statement types, and cursor types that are currently active on the connection.
- Use an earlier version of the SQL Server provider, such as the version that was released in MDAC 2.5 Service Pack 1.
- Do not use client-side cursors.
- Use statements that generate cursors, rather than firehose-mode statements.
- Avoid transactional contexts, if possible.
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 Microsoft Data Access Components 2.6 Service Pack 1.
This behavior occurs when you query against SQL Server 7.0 because the server does not return complete meta data for certain types of statements. This has been seen when using the GROUP BY clause (as shown below), and when querying views that contain the DISTINCT clause. SQL Server 2000 returns more meta data to the client, so the client cursor engine does not attempt to re-query the server.
The following shows sample output from a SQL Profiler trace:
Event Class Event Sub Class Text Connection ID SPID+DTCTransaction enlisting 05fd00a7-3b2a-11d5-aea6-0080c7c71171 1158468 9+DTCTransaction active 05fd00a7-3b2a-11d5-aea6-0080c7c71171 1158468 9+DTCTransaction propagate transaction 05fd00a7-3b2a-11d5-aea6-0080c7c71171 1158468 9+SQL:BatchCompleted SELECT au_lname from authors GROUP BY au_lname 1158468 9 Connect 1158472 10 ExistingConnection 1158472 10+SQL:BatchCompleted SET NO_BROWSETABLE ON 1158472 10+RPC:Starting sp_prepare @P1 output, NULL, N'SELECT * FROM Authors', 1 select @P1 1158472 10+RPC:Completed sp_prepare @P1 output, NULL, N'SELECT * FROM Authors', 1 select @P1 1158472 10 Disconnect 1158472 10 Connect 1158473 10 ExistingConnection 1158473 10 DTCTransaction get address 1158473 10+DTCTransaction idle 05fd00a5-3b2a-11d5-aea6-0080c7c71171 1158473 10 DTCTransaction propagate transaction 1158473 10+RPC:Starting [pubs]..sp_primary_keys_rowset N'Authors', NULL 1158473 10+RPC:Completed [pubs]..sp_primary_keys_rowset N'Authors', NULL 1158473 10+DTCTransaction drain abort 05fd00a7-3b2a-11d5-aea6-0080c7c71171 1158473 10+DTCTransaction aborting 05fd00a7-3b2a-11d5-aea6-0080c7c71171 +DTCTransaction idle 05fd00a7-3b2a-11d5-aea6-0080c7c71171 1158473 10+DTCTransaction propagate transaction 05fd00a7-3b2a-11d5-aea6-0080c7c71171 1158473 10This shows that a simple SELECT statement with a GROUP BY clause is executed on a session. It is briefly prepared (in an attempt to collect meta data), and then a new connection is made for requesting primary key information from the table. The new connection is (incorrectly) enlisted in the transaction, which aborts immediately after the sp_primary_keys_rowset call is made.
After the drain abort entry, the application stops responding for the length of the transaction timeout.
Steps to Reproduce Behavior
- Create a Visual Basic ActiveX DLL project with one class and one function, and add a reference to the Microsoft ActiveX Data Objects version 2.6 library.
- Paste the code below into the function. Note that you need to change your connection string according to your situation.
- Right-click the class that you created and click Properties. For the MTSTransactionMode select 2 - RequiresTransaction.
- Compile the DLL and register it under MTS/COM+.
- Create a client that calls the new class. To do this, create a new Microsoft Visual Basic Standard EXE project, and paste the following code in the project:
- Start SQL Profiler and connect to your SQL Server. For Events, add the Session (Connect, Disconnect, and Existing Connections) event, the Transactions (DTCTransaction) event, and the TSQL (RPC:Starting, RPC:Completed, and SQL:BatchCompleted) event. For data columns, include the Event Class, Event Sub Class, Text, Connection ID, and SPID columns.
- Run the Visual Basic client, and observe the output in SQL Profiler. When the cursor engine attempts to collect primary key information, a drain abort entry is made and the application stops responding.
Dim rs as New ADODB.RecordsetDim connStr as String'You must change the User ID <username> value and the password =<strong password> value 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.connStr = "Provider=SQLOLEDB;Data Source=YourServer;User ID=<user name>;Password=<strong password>;Initial Catalog=Pubs;"rs.CursorLocation = adUseClientrs.Open "SELECT au_lname from authors GROUP BY au_lname", connStr, adOpenKeyset, adLockBatchOptimistic, adCmdTextrs.CloseSet rs = Nothing
Dim obj as ObjectSet obj = CreateObject("YourComponent.YourClass")obj.YourMethod
dtc distributed transaction coordinator enlist enlistment sql server provider sqloledb hang timeout drain abort group by distinct view metadata com+ mts
Article ID: 297266 - Last Review: 01/10/2015 13:05:41 - Revision: 4.2
Microsoft OLE DB Provider for SQL Server 2000 2000.80.194, Microsoft Data Access Components 2.6
- kbnosurvey kbarchive kbhotfixserver kbqfe kbbug kbdatabase kbfix kbmdac260sp1fix kbqfe KB297266