Knowledge Base

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:

#include "stdafx.h"
#include "windows.h"
#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoeof")

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

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

    _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

Microsoft KB Archive/325338

Contents