I want to be able to cancel queries that take too long in a winforms app,
yet it's not clear to me how to do this cleanly. I want to do it in a way
that the server immediately stops execution, and not just free the client
up. Can anyone provide me with an example of the "right" way to do this?

TIA,
Bob

hi,

if you are using MS SQL .. there is this system stored procedure "SET

"Bob" wrote:

> I want to be able to cancel queries that take too long in a winforms app,
> yet it's not clear to me how to do this cleanly. I want to do it in a way
> that the server immediately stops execution, and not just free the client
> up. Can anyone provide me with an example of the "right" way to do this?
>
> TIA,
> Bob
>
>
>

hi,

If you are using MS SQL .. then there is this system stored procedure
"sp_Configure" where you can set the SET QUERY_GOVERNOR_COST_LIMIT statement
to specify x number of second that the long query will run ..SQL server uses
your hardware configuration capability and based on estimate to decide if
your query will exceed the setting and hence don't even attempt to run your
query if it determine it is too long running...this setting can also be set
by connection only ...
I think this is "cleanest" way ... no major coding needed .. just put your
GetDataSet() in the try catch loop to confirm the return..

Albert




"Bob" wrote:

> I want to be able to cancel queries that take too long in a winforms app,
> yet it's not clear to me how to do this cleanly. I want to do it in a way
> that the server immediately stops execution, and not just free the client
> up. Can anyone provide me with an example of the "right" way to do this?
>
> TIA,
> Bob
>
>
>

Not acceptable. There are plenty of legitimate queries that need to be
allowed to run a while, and other, shorter ones that an end user may want to
cancel. Unfortunately I think the solution will require some 'major', or at
least multithreaded coding, with which I'm not very experienced.

I suppose I could do some empirical tests to see if server load drops away
immediately on killing a thread running the query, but it doesn't give me a
warm fuzzy to take a shot in the dark doing something like that, I
definitely want to be pointed to a 'best practice' from someone who knows
more than me - both on the treading side and what happens server-side.

Bob

"Albert" <(E-Mail Removed)> wrote in message
news:630B054F-F3B0-4E96-9A9B-(E-Mail Removed)...
> hi,
>
> If you are using MS SQL .. then there is this system stored procedure
> "sp_Configure" where you can set the SET QUERY_GOVERNOR_COST_LIMIT
statement
> to specify x number of second that the long query will run ..SQL server
uses
> your hardware configuration capability and based on estimate to decide if
> your query will exceed the setting and hence don't even attempt to run
your
> query if it determine it is too long running...this setting can also be
set
> by connection only ...
> I think this is "cleanest" way ... no major coding needed .. just put your
> GetDataSet() in the try catch loop to confirm the return..
>
> Albert