Article ID: 297266 - View products that this article applies to.
This 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:
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:
(http://support.microsoft.com/kb/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.
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:
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.
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
After the drain abort entry, the application stops responding for the length of the transaction timeout.
Steps to Reproduce Behavior
Article ID: 297266 - Last Review: September 26, 2005 - Revision: 4.2
Contact us for more help