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

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
				

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	
				

Back to the top

Steps to Reproduce the Behavior

1. 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
   					

2. 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;
   }
   					

Back to the top