Microsoft KB Archive/297266

= FIX: SQLOLEDB: Incorrect Transaction Enlistment Causes Hang and Drain Abort Entry =

Article ID: 297266

Article Last Modified on 9/26/2005

-

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 value and the 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 = &quot;Provider=SQLOLEDB;Data Source=YourServer;User ID= ;Password= ;Initial Catalog=Pubs;&quot; rs.CursorLocation = adUseClient rs.Open &quot;SELECT au_lname from authors GROUP BY au_lname&quot;, connStr, adOpenKeyset, adLockBatchOptimistic, adCmdText rs.Close Set rs = Nothing Dim obj as Object Set obj = CreateObject(&quot;YourComponent.YourClass&quot;) obj.YourMethod

Additional query words: dtc distributed transaction coordinator enlist enlistment sql server provider sqloledb hang timeout drain abort group by distinct view metadata com+ mts

Keywords: kbbug kbfix kbqfe kbmdac260sp1fix kbdatabase kbhotfixserver KB297266

-

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

© Microsoft Corporation. All rights reserved.