FIX: SQLOLEDB: Incorrect Transaction Enlistment Causes Hang and Drain Abort Entry (297266)


The information in this article applies to:

  • Microsoft OLE DB Provider for SQL Server 2000 2000.80.194
  • Microsoft Data Access Components 2.6
This article was previously published under Q297266

SYMPTOMS

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.
If SQL Profiler is used to monitor the client activity, a drain abort entry appears in the Event Sub Class data column, and the associated Transaction ID appears in the Text data column. The application then stops responding until the transaction timeout has passed.

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.

CAUSE

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.

RESOLUTION

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

Hotfix

The 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

WORKAROUND

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

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 Microsoft Data Access Components 2.6 Service Pack 1.

MORE INFORMATION

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        10
This 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.Recordset
Dim 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 = adUseClient
rs.Open "SELECT au_lname from authors GROUP BY au_lname", connStr, adOpenKeyset, adLockBatchOptimistic, adCmdText
rs.Close
Set rs = Nothing
					
    Dim obj as Object
Set obj = CreateObject("YourComponent.YourClass")
obj.YourMethod


Modification Type:MinorLast Reviewed:9/26/2005
Keywords:kbHotfixServer kbQFE kbbug kbDatabase kbfix kbMDAC260sp1Fix kbQFE KB297266 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.