|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 297266 - Last Review: September 26, 2005 - Revision: 4.2 FIX: SQLOLEDB: Incorrect Transaction Enlistment Causes Hang and Drain Abort EntryThis article was previously published under Q297266 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. 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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=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.
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 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
APPLIES TO- Microsoft OLE DB Provider for SQL Server 2000 2000.80.194
- Microsoft Data Access Components 2.6
| kbhotfixserver kbqfe kbbug kbdatabase kbfix kbmdac260sp1fix kbqfe KB297266 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |