Microsoft KB Archive/325338

= FIX: OLE DB Provider for SQL Server Does Not Cancel a Batch Statement When You Call the Cancel Method =

Article ID: 325338

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft OLE DB Provider for SQL Server 2000 2000.81.7713.0
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.194
 * Microsoft OLE DB Provider for SQL Server 2000 2000.80.380.0
 * Microsoft OLE DB Provider for SQL Server 7.0

-



This article was previously published under Q325338



SYMPTOMS
When you call the Cancel method on an ADO Connection or Recordset object, Microsoft OLE DB Provider for SQL Server (SQLOLEDB) does not cancel a query that is asynchronously executed and that involves a batch statement. Instead, Microsoft OLE DB Provider for SQL Server calls the SQLMoreResults method to fetch the remaining results.



CAUSE
This problem occurs because of a code problem in which OLE DB Provider for SQL Server calls SQLMoreResults.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

The English version of this fix 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 (in bytes)  File Name 27-Jun-2002  2000.81.9001.8       450,560          Sqloledb.dll

NOTE: This hotfix is for Microsoft Data Access Components (MDAC) version 2.7 only. If you are using an earlier version of MDAC, you must upgrade your MDAC version to 2.7 before you apply this hotfix.



WORKAROUND
There is no workaround for this problem if the query includes a batch statement.



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 SQL Server 2000 Service Pack 3.



MORE INFORMATION
This problem does not occur with a single SELECT statement. For example, review the following sample SQL Profiler output:

Single Statement SQL:BatchCompleted  select * from master..testtable where i <> 0         sa  20  61  0   1763    496 53  2002-06-21 16:56:24.073 Attention                       sa                 496 53  2002-06-21 16:56:25.847 Audit Logout               sa  20  71  0   2173    496 53  2002-06-21 16:56:23.833 Batch Statement SQL:BatchCompleted declare @value1 as int set @value1 = 0 select * from master..testtable where i <> @value1           sa  771 858 0   2923    2420    53  2002-06-21 16:51:46.680 Audit Logout               sa  771 868 0   2953    2420    53  2002-06-21 16:51:46.660 With a single statement, an ATTENTION event is fired (which differs from the behavior of a batch statement). The ATTENTION event shows that the query has been canceled.

Steps to Reproduce the Behavior
  Create a table in a Microsoft SQL Server database (such as the Pubs database), name the table testtable, and then add rows by using the following script: create table testtable(i int) go declare @r int select @r=1 while (@r<=500000) begin insert into testtable values(@r) select @r=@r+1 end   Create new Microsoft Visual C++ ActiveX Data Objects (ADO) application, and then add the following code to this application:
 * 1) include &quot;stdafx.h&quot;
 * 2) include &quot;windows.h&quot;
 * 3) import &quot;c:\program files\common files\system\ado\msado15.dll&quot; no_namespace rename(&quot;EOF&quot;,&quot;adoeof&quot;)

int main(int argc, CHAR* argv[]) {           CoInitialize(NULL); try {   _ConnectionPtr conn(__uuidof(Connection)); _bstr_t connStr(&quot;provider = sqloledb; data source = myserver;initial catalog=myDB&quot;); conn->Open(connStr,&quot;sa&quot;,&quot;mdac&quot;,adConnectUnspecified); conn->CursorLocation = adUseServer;

_bstr_t mysql(&quot;declare @value1 as int \n&quot;       &quot;set @value1 = 0 \n&quot;        &quot;select * from testtable where i <> @value1&quot;);

_variant_t affect; conn->Execute(mysql, (VARIANT *)&affect, adAsyncExecute); Sleep(2000); conn->Cancel; conn->Close; }           catch(_com_error &e) {    _bstr_t msg = e.ErrorMessage ; }         return 0; }                   

Additional query words: Cancel Async batch stmt

Keywords: kbhotfixserver kbqfe kbsqlserv2000sp3fix kbbug kbfix kbsqlserv2000presp3fix KB325338

-

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

© Microsoft Corporation. All rights reserved.