Long Running Stored Procedure from ADO.NET or Classic ADO

Question

Assume the Stored Proc takes 10 minutes to run and returns no data.

What is the proper way to call a stored procedure in SQL Server and have your code not wait around for the result? Is there a way to handle it from the T-SQL or the connection? Something that works in ADO.NET and classic ActiveX ADO?

The only way I thought of is:
1) Create a Job in your T-SQL
2) Load your T-SQL based code into Step one of the Job
3) Make sure the last line of your code removes the Job
4) Execute Job (I'm pretty sure this doesn't leave you hanging for a response) I know this is very bad and hackish... but

Is there some other T-SQL that I'm not thinking of that you can wrap a stored proc in to say "I know there is no response... so I'm choosing not to wait."?

Answer 1

Run it in a separate thread and just let the thread die.

Answer 2

For .Net, try the .BeginExecuteNonQuery() method of your SqlCommand object. Not sure how that would work from ActiveX ADO, though.

Answer 3

Yes, you can

1. call it asynchronously, using SqlCommand.BeginExecuteNonQuery()
2. Or call it asynchronously on a delegate (will use trhead from thread pool),
3. or on a separate thread (created yourself)

4. In Classic ADO, Use the Option parameter in the Connection's Open method:

   oConnection.Open( , , , adAsyncConnect)

Answer 4

I would investigate rewriting any SP that took that long unless it was making changes or inserting a very large number of records. If you have an sp taking more than 30 seconds from the user interface, I's say you have a very strong possibility of a performance problem. Does the proc in question have a cursor? If you so you can probably get it down to millseconds with proper performance tuning. I would alawys consider performance tuning before doing a workaround to make the user interface go on without the proc finishing.

You say it returns no records, but what would the user need to know if it fails? If they have gone on to something else, they will think it has successfully completed.