How do you cancel a SQL sproc call?

[kempis555]

It would be helpful if there were a way to cancel a sproc call using VB.

I'm having a problem with a bad sproc which causes deadlocks if a user runs a page, gets impatient and closes the browser, and then runs the page again.

However, I can also see a use for cancelling a sproc call in other projects that I've made.... like if the user makes a poor SQL search and there are going to be a lot of rows returned.

I use VB.NET with code-behinds and a SQL2k database.

-k

 

[ca8msm]

Is there actually a question in your post? You say it would be helpful so I'm presuming you are asking if there is a way?

If so, then not really as if the user has simply closed their browser then how is the web server (let alone the database) supposed to know that this has happened? You could maybe set a timeout for the SP that you would be happy with.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[kempis555]

I was thinking perhaps a javascript.OnClose() could call a script which would cancel any sproc calls... or maybe the sproc call could be in a spawned thread which could be accessed somehow and commanded to stop whatever sproc calls are running.

Those are just general ideas, though... I have no idea if a connection object instantiated by one script can be accessed by another, or whether the connection can ask the SQL server to abort a running sproc.

-k

 

[ca8msm]

You could use a javascript function that fired when the page unloaded to pop up another page but you'll find that a lot of people block popups anyway so it probably won't be effective.

I really don't think you'll have much luck apart from maybe with the timeout property.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chiph]

You can't reliably detect when a user closes their browser (they could kill their firefox.exe process).

I would look at redesigning the stored proc so that it doesn't cause the deadlocks. I don't think a timeout will work for you, as the web server will (eventually) detect the closed socket connection, free the worker thread/process, which will hard-close the connection to the database, thus causing your problem again (depending on who times out first).

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[kempis555]

Yeah, I think fixing the SQL is the only way to go.

I tried I puting the object which does the SQL connection into a thread, and then overriding the Finalize() method of the object. This Finalize() was automatically called when I called the thread.abort().
Inside the Finalize() method I tried using SQLDataAdapter.Cancel(), SQLConnection.close(), setting them to "Nothing", all sorts of things... that didn't work, the SQL sproc continued to run anyways.

Blah, I give up. Thanks guys.

-k