Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 8 down vote favorite

We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.

I have a second stored procedure that should be run after the first stored procedure runs since it is based on the numbers obtained from this first stored procedure, however it takes longer to run and is for a separate process, so I don't want to make the user wait while this 2nd stored procedure gets ran.

Is there a way to have one stored procedure start a second stored procedure, and return immediately without waiting for results?

I'm using SQL Server 2005.

share|improve this question
How are the stored procedures being called? ASP.NET web application? SSRS? – Mr.Brownstone Feb 18 at 13:59
4  
@Mr.Brownstone It's usually called from an ASP.Net web application, although it may be being called by more than one of them. I'd have to double-check. It also occasionally gets run manually from SSRS. – Rachel Feb 18 at 14:19
@MartinSmith I've worked once with the SQL service broker in the past, and was hoping there would be a simpler way. It seems like such a complex setup for something as simple as this. – Rachel Feb 18 at 14:20
1  
@MartinSmith that is pretty much what I was thinking - also, as for SSRS no you can't but what you could do is incorporate Report Viewer into the application and move your rdl's into it - that way it would be possible to make async calls for the reports as well. – Mr.Brownstone Feb 18 at 14:35
show 4 more comments

4 Answers

up vote 8 down vote accepted

It looks like there are multiple ways to accomplish this, but I found the simplest way was Martin's suggestion of setting up the procedure in a SQL job, and starting it using the asynchronous sp_job_start command from my stored procedure. 

EXEC msdb.dbo.sp_job_start @job_name='Run2ndStoredProcedure'

This only works for me because I don't need to specify any parameters for my stored procedure.

Other suggestions that may work depending on your situation are

  • Using the SQL Service Broker like Martin and Sebastian suggest. This is probably the best suggestion if you don't mind the complexity of setting it up and learning how it works.

  • Running the process asynchronously in the code that is responsible for executing the stored procedure, like Mr.Brownstone suggested.

    Not a bad idea, however in my case the stored procedure gets called from multiple places, so finding all those places and ensuring they call the 2nd procedure too didn't seem that practical. Also, the 2nd stored procedure is fairly critical, and forgetting to run it could cause some major problems for our company.

  • Make the 1st procedure set a flag, and setup a recurring job to check for that flag and run if its set, like Jimbo suggested. I'm not a big fan of jobs that run constantly and check for changes every few minutes, but it certainly is an option worth considering depending on your situation.
share|improve this answer
2  
Have a look at Asynchronous Procedure Execution for an ready-to-use example using Service Broker. The advantages over sp_job is that it works on Express Edition and is entirely DB contained (no dependency on MSDB job tables). The later is very important in DBM failover and on HA/DR recovery. – Remus Rusanu Feb 18 at 21:29
Shoot, I see Martin linked the same article. I'll leave the comment for the failover/DR arguments. – Remus Rusanu Feb 18 at 21:31
@RemusRusanu: well, that's one of the best sources of information regarding Service Broker, but I suppose you knew already ;-). – Marian Feb 19 at 18:07
up vote 5 down vote

You could use service broker together with activation on the queue. With that you could post the parameters for the procedure call on the queue. That takes about as much time as an insert. After the transaction is committed and potentially a few more seconds, activation would automatically call the receiver procedure asynchronously. It than just wuold have to take the parameters of the queue and do the desired work.

share|improve this answer
up vote 1 down vote

Yes, one method:

  1. When the 1st stored procedure is complete it inserts a record with all the information needed for the second stored procedure to run
  2. The second stored procedure runs as a job, every minute or what time you decide
  3. It checks for inserted records, does its process and marks the record as complete
share|improve this answer
This limits the execution of the stored procedure to the number of jobs that you have running which if you only ever want this to be called by one client at a time is fine - whereas you may have multiple clients calling the same procedure at the same time. Also, how would the client know the job has completed without repeatedly polling the database to find out if a flag has been set? – Mr.Brownstone Feb 18 at 14:16
1  
@Mr.Brownstone - Potentially the job could process more than one outstanding task queued up by different stored procedure calls when it runs. The stored procedure could also possibly call sp_start_job to start it off or create jobs as needed dynamically to avoid polling every minute but complexity for that case probably means it will be no simpler than service broker. – Martin Smith Feb 18 at 14:25
@MartinSmith I actually already have a job setup for this 2nd stored procedure since it used to be run nightly until we found some problems with it not being synced correctly with the 1st procedure's numbers. If I start the job from the 1st stored procedure, would it run asynchronously and return from the SP immediately? – Rachel Feb 18 at 14:40
@Rachel - Yes. sp_start_job returns immediately. Can't remember what permissions it needs though. – Martin Smith Feb 18 at 14:41
1  
Starting a job from another procedure / database is a fairly complex problem if you don't want to open up big security holes. Erland Sommarskog has an article on the different techniques that you need to combine:sommarskog.se/grantperm.html However, there is no complete solution in there. – Sebastian Meine Feb 18 at 15:02
up vote 0 down vote

Another possibility would be to get the 1st stored procedure to write to an audit table when it completes and place a trigger on the audit table that launches the 2nd stored procedure when the audit table is written to. No need to continuously poll and no need for an extra SQL Server Agent job.

share|improve this answer
1  
The first stored procedure wouldn't return until the insert to the audit table completes and that wouldn't happen until the trigger finished executing (including the call to the 2nd stored procedure) – Martin Smith Feb 18 at 21:26
I already looked into using a Trigger, but triggers are run synchronously with the INSERT or UPDATE statement, not asynchronously, so Martin is correct that the 1st procedure would still end up waiting until the 2nd procedure finishes to return. – Rachel Feb 19 at 12:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.