Re: How to really cancel an asynchronous Connection.Execute

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 08:35:38 -0800

When you cancel an operation, you're asking the engine to undo what you
asked it to do. If it's an update or other action query it has to roll back
the work. If it's a long-running operation (and it sounds like it is) there
is a lot of work to do. It also might be blocked by some other operation
going on which means it can neither proceed or rollback until it times out.
In the early days we could set a short timeout and when these occurred we
could (at our own discretion) continue or abandon the operation. I would
close the connection and trap the exception and move on. The server will
take care of itself (if it can). Note that some operations can't be rolled
back and must be completed to maintain referential integrity.

hth 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Ian Boyd" <ian.msnews010@avatopia.com> wrote in message
news:Ols4zo2DEHA.2052@TK2MSFTNGP11.phx.gbl...
> >>>> From: Ian Boyd (ian.msnews009@avatopia.com)
> >>>> Subject: Anything i can do to help Connection.Cancel come back
faster?
> >>>> Date: 2003-08-31 12:18:56 PST
>
> i'm executing a query asynchronously though an a Connection.Execute
method.
>
> The Cancel method is synchronous, meaning that my software is stuck until
> SQL Server or ADO decides it is ready.
>
> i've tried simply calling Connection.Close to simply break my connection
to
> SQL Server, but ADO throws an exception at that.
>
> Is there something i can do to speed up the Cancel? i've tried boosting my
> thread's priority, but that didn't do anything.
>
> And i'm fresh out of ideas.
>
>
> >>>> From: Ian Boyd (ian.msnews009@avatopia.com)
> >>>> Subject: What's the proper way to Cancel and asynchronous execute and
> fetch?
> >>>> Date: 2003-08-26 06:25:10 PST
>
> i'm trying to run a complicated query, that will return quite a few rows.
>
> i want to be able to Connection.Cancel the execution at any time.
>
> What is the proper combination of
>     Connection.CursorLocation, adAsyncExecute, adAsyncFetch,
> adAsyncFetchNonBlocking
>
> i tried
>     Connection.CursorLocation := adUseClient;
>     Connection.Execute(, , adAsyncExecute);
>
> and
>
>     Connection.CursorLocation := adUseClient;
>     Connection.Execute(, , adAsyncExecute | adAsyncFetch);
>
> In both cases, i can Cancel the execution if i cancel it shortly into the
> execute. If i wait too long, it doesn't cancel. Presumably, this is me
being
> allowed to cancel the running query on SQL Server, but once results are
> coming back, i cannot cancel it until they are all returned (which takes a
> while).
>
> i've investigated articles
>     KB194960
>     KB190988
>     KB229799
>     KB224332
>     KB262311
>
> as well as Googled newsgroup articles.
>
> All the above conflict, or their methods don't actually work.
>
> i want to know the MS intended way to do it.
>
>
> >>>> From: Ian Boyd (ian.msnews008@zunblvlda1.dyndns.org)
> >>>> Subject: How to really Cancel an asynchronous Execute?
> >>>> Date: 2003-10-19 09:54:51 PST
>
> i asynchronously run a query that will take a long time to return using
> Connection.Execute.
>
> i would then like to be able to Cancel the asynchronously executing query.
>
> i call Connection.Cancel, but Cancel doesn't really cancel it (i.e. i can
> still wait up to 3 minutes on some queries). It's like it is just going to
> let the query finish running anyway.
>
> Is there a way i can REALLY cancel the query. i try freeing the Connection
> object, but it complains about trying to free while an operation is going
> on. i've thought about killing the thread, but then the objects won't have
a
> change to clean up.
>
> Is there some way to cancel an asynchronously running query?
>
>
> >>>> From: Ian Boyd (ian.borlandnews009@avatopia.com)
> >>>> Subject: Cancelling asynchronous queries - HOW
> >>>> Date: 2003-08-26 10:47:23 PST
>
> How do i cancel an asynchronously running and fetching query?
>
> Consider
>
> procedure TfrmMain.bbStartQueryClick(Sender: TObject);
> begin
>     ADOQuery1.Connection := ADOConnection1;
>     ADOQuery1.CursorLocation := clUseServer;
>     ADOQuery1.CursorType := ctOpenForwardOnly;
>     ADOQuery1.ExecuteOptions := [eoAsyncExecute, eoAsyncFetch];
>
>     ADOQuery1.SQL.Text := Memo1.Lines.Text;
>     ADOQuery1.Open;
>
>     //The call to open returns immediatly (i.e. asynchronously)
> end;
>
> procedure TfrmMain.bbCancelQueryClick(Sender: TObject);
> begin
> //    ADOConnection1.Cancel; //nope - exception
> //    ADOQuery1.Close; //nope - exception
> //    ADOQuery1.Recordset.Close; //nope - exception
> //    ADOQuery1.Recordset.Cancel; //nope - exception
>
> //    Insert your genius here
>
> //    ^^^^^^^^^^^^^^^
> end;
>
>
> >>>> From: Ian Boyd (ian.msnews009@avatopia.com)
> >>>> Subject: How to really cancel an asynchronous Connection.Execute
> >>>> Date: 2004-03-21 12:28:03 EST
>
> It's now 8 months later, and i still don't have an answer.
>
>

Relevant Pages

  • How to really cancel an asynchronous Connection.Execute
    ... i'm executing a query asynchronously though an a Connection.Execute method. ... Is there something i can do to speed up the Cancel? ... Connection.CursorLocation, adAsyncExecute, adAsyncFetch, ...
    (microsoft.public.data.ado)
  • Re: Return to form if query unmatched
    ... Dim MyRecCount As Long ... If it doesn't pop up a message and set Cancel = True. ... record source of that form. ... If it is a sql then you should create a query with that sql, ...
    (microsoft.public.access.queries)
  • Re: Return to form if query unmatched
    ... The final solution I have gone with is using my original query as datasource ... Dim MyRecCount As Long ... If it doesn't pop up a message and set Cancel = True. ...
    (microsoft.public.access.queries)
  • RE: check qry on form close for null entrys
    ... criteria I just put Datebut it still gives me dates from way back. ... If you are using the query builder, don't put Null for each field in the ... show the MsgBox and not restrict the person from closing. ... cancel the close and present the records will Nulls in the form. ...
    (microsoft.public.access.gettingstarted)
  • RE: check qry on form close for null entrys
    ... If you are using the query builder, don't put Null for each field in the ... Put each Null criteria on a different line. ... show the MsgBox and not restrict the person from closing. ... cancel the close and present the records will Nulls in the form. ...
    (microsoft.public.access.gettingstarted)