5

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.

Improve this question
1

3 Answers 3

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I was just looking at this article as well, this seems to take care of my problem. Thank you for the quick responses.
I m using oracle and with Oracle client i can't use Asynchronous procedure execution! so ?? creating a job within sql to have it execute that stored procedure would be the best approach ?
@frank if you have questions ask them as a separate question.
i did ... stackoverflow.com/questions/16737525/…
3

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

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

For this instance I am unablet o use the BeginExecuteNonQuery because I am not using an SqlCommand to execute my stored procedure, I am using DataSet Templates to manage my sql scripts, which also makes it easier to work with. But thanks !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.