Execute a stored procedure from a windows form asynchronously and then disconnect?

Question

I am calling a stored procedure from my application that can take 30 minutes to execute.

I don't want to make my user leave the application open for that entire time period. So I would like to call the sproc, let it fly, and let them shut down the application and come back later.

How can I do this?

Solution 1

This is actually a quite common scenario. You cannot do anything client based because the client may go away and disconnect and you'll lose the work achieved so far. The solution is to use Service Broker Activation: you create a service in the database and attach an activated procedure. In your application (or ASP page) you send a message to the service and embed the necessary parameters for your procedure. After your application commits, the message activates the service procedure. the service procedure reads the parameters from the message and invokes your procedure. since activation happens on a server thread unrelated to your original connection, this is reliable. In fact the server can even shutdown and restart while your procedure is being executed and the work will be rolled back then resumed, since the activating message will trigger again the service procedure after the restart.

Update

I have published the details of how to do this including sample code on my blog: Asynchronous procedure execution.

Solution 2

You can use the BeginExecuteXXX/EndExecuteXXX methods (depending whether it returns a result or not) of the SqlCommand, passing a callback delegate.

Solution 3

If you really want to close down your application completely, I suggest you define a job in SQL Server Agent, and just execute a T-SQL statement to start that job manually. The syntax is:

sp_start_job 
     {   [@job_name =] 'job_name'
       | [@job_id =] job_id }
     [ , [@error_flag =] error_flag]
     [ , [@server_name =] 'server_name']
     [ , [@step_name =] 'step_name']
     [ , [@output_flag =] output_flag]

The job would execute your stored procedure. You will have to be a little creative to pass in any arguments. For example, insert the parameters into a "queue" table and have the job process all the rows in the queue.

Instead of a job, an insert trigger on your queue should work as well.

Solution 4

I suggest a re-architecture. Create a "work queue" table where you log requests to run the stored procedure. Then either have a Windows Service or a SQL Server job check that work queue from time to time (or be really ingenious and use a trigger) to kick off the stored procedure. Have the stored procedure update the progress from time to time in the work queue table, and your front-end can look at that an tell the user the progress, and then display the results when they're done.

Solution 5

I prefer to use a background service for offline processing, where your user app tells the service what to do and then disconnects. The service can log elapsed times and errors/status, and restart if necessary. WCF is designed for this and supports queues to communicate with.

View more solutions

Comments

[Joshua]

I agree, if you want your application to be able to close you'll need to completely move the sproc call out of the original process.

[Joshua]

I don't know why people down-vote without explaining why. This is a perfectly valid answer if they wanted to execute the procedure without fully shutting down the program.

[Admin]

I didn't mean to down-vote if I did that (new to this forum, so I may have clicked on accident and didn't realize what I was doing). I agree, perfectly good solution...but alas, I do want to let them completely kill the app. Thx Mehrdad.

[Admin]

Well, I guess it defintely wasn't me...I just tried to vote and I would have to log in to do so (and I'm not). Anyway, it was helpful.

[Admin]

Well, I am not going to log in or register (sort of disappointed that I can't vote or close this out without doing that). Anyway, this was the best answer for me and then got backed up by a few supporting posts. All accept for the "...buy faster hardware" bit. I'd just assume claim failure, than tell my client they need to buy faster hardware, because I can't get it done right. Thanks for all your help!

[Remus Rusanu]

@Scot, you are basically describing a built in feature: Service Broker Activation. Both Internal and External activation are arguably better than a windows service or an Agent job checking from time to time.

[Remus Rusanu]

You can't have any sort of reliable async execution based on BeginExecute for long running tasks. The client process has to stay around, maintaining an open connection, until the execution finishes. The async execution of SqlClient is geared toward scalable, nonblocking, execution of normal database access, not toward launching long running querries.

[mmx]

I don't know what exactly you mean from "reliability" here. If I want to submit jobs to SQL server to reliably perform without expecting any results, I'd go with something like MSMQ or service broker but there are plenty of cases where you actually expect a result (e.g. generating a long running report) that you do want to perform in the background where Begin/End methods make perfect sense.

[Ed Power]

Remus had a good answer, and I appreciate him taking the time to post the blog article. That would be much less work than creating a separate background service if it's applicable to your problem.

[tony722]

Yes! Thanks very much. One thing to make sure of: The service broker needs to be enabled for the database. (Database Properties -> Options in SSMS) or ALTER DATABASE ... SET ENABLE_BROKER. The nice thing is this even works in Express Editions of SQL Server!

[zaitsman]

@Remus Rusanu, i have tried the code from your article (seems comments are disabled there) and came across a weird behaviour. If i write a stored proc to INSERT INTO a table in my db and then WAITFOR some then running it through the ups_AsyncExecInvoke i get my whole table blocked while the proc executes (e.g. i can no longer do SELECT on that table); if i run the same proc directly (without the Broker message) the table doesnt block. Any ideas?

[Remus Rusanu]

@zaitsman: There isn't anything specific to activation that should change the behavior. But activated procedures execute under session settings that may differ from a user session because client drivers do set some settings on their own. These differences can result in a different execution plan, one that can use different locking strategy. You will have to investigate what happens. If you have a consistent repro, ask a new question here, post the repro code, and I'll have a look.

[zaitsman]

@RemusRusanu, never mind, i missed the BEGIN Transaction in your blog post; since the whole stored proc was part of that, the table was being locked during the execution.