CODEWITHSUNDEEP
asp.netsqlsql-servervb.net
Yesuah Iniguez
Yesuah Iniguez

Reputation: 105

Execute sql script and not wait for completion

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.

Upvotes: 5

Views: 12171

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294247

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.

Upvotes: 4

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

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

Upvotes: 3

Damian Edwards
Damian Edwards

Reputation: 7339

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.

Upvotes: 0

Related Questions