Interrupt SQL Query

[Guest]

I'm using VS2005 Winforms. I want a method to allow the user to be able to
interrupt an SQL query if he decides he doesn't want to wait for it any
longer.

I've used IAsyncResult to open an async connection that runs an
ExecuteNonQuery, but this method doesn't allow interruption
(EndExecuteNonQuery gets blocked until the execute finishes), and ideally I'd
like to interrupt a Query rather than a NonQuery.

It seems to me this is a generic need that would be of value to many
developers.

Any ideas, article references, etc. would be greatly appreciated. If your
answer is SQL Server specific that would be fine, but my current project is
against Informix, so ideally I'd like the solution to be .Net generic.

Thanks in advance.

 

[Kevin Spencer]

Can't be done. Sending a query to a database is like a browser sending an
HTTP request to a web server. Once it's sent, you can't "unsend" it. The
database will continue to execute the task until it is complete. However,
like a browser, you can stop listening for a response and do something else.
Calling the database asynchronously will have the same effect, allowing your
app to forget about the response and continue working.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

 

[Guest]

You may be right. However:

1) Queries issued from MSSQL Query Analyzer seem to be interruptable. Is
there perhaps a "call-back" capability that allows a client to tell the
server to stop a particular process?

2) If I follow your approach, do you have experience issuing an
EndExecuteNonQuery while an async command thread is still active?
Documentation says that it is "blocked" until the thread completes. Will
sending EndExecuteNonQuery early eventually "clean up" the thread so I don't
leave orphan threads?

3) Does anyone know of white papers or chapters in books that addresses this
fairly general need to interrupt Queries and stored procedures?