Execute sql script and not wait for completion

Question

I have a sql stored procedure that runs for about 3 minutes, I am looking to execute this stored procedure from asp.net, but I know that if I do, that asp.net will most likely time out.

I'm thinking of just creating a job within sql to have it execute that stored procedure, and have asp.net call a stored procedure to call that job. I have a table that is updated when the stored procedure starts, and when it ends.

My application will use this to determine when the script has finished, however, I wanted to know if there is another way to run the stored procedure and not have it wait for it to finish to push a response back.

I just want to know if there is a more efficient way to do this, or if I should just stick to creating jobs for scripts that take forever to run.

Answer 1

If you don't care about the result, you can just use the SqlCommand.BeginExecuteNonQuery() method. This will fire of the command on a background thread. More info on MSDN.

Answer 2

Sounds like this would be a good candidate for using Service Broker.

Answer 3

Check out this article: Asynchronous procedure execution. The articles gives code example, and explains why leveraging internal activation is better than relying on a SQL Agent job. Running procedures like this is reliable (unlike the ADO.NEt async BeginExecuteXXX, the execution is guaranteed even if the client disconnects) and the execution will occur even after a SQL Server restart and even after a disaster recovery server rebuild.