45

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.

Improve this question
9
  • How are the stored procedures being called? ASP.NET web application? SSRS?
    – World Wide DBA
    Feb 18 '13 at 13:59
  • 5
    You can use service broker for this
    – Martin Smith
    Feb 18 '13 at 14:12
  • @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 '13 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 '13 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.
    – World Wide DBA
    Feb 18 '13 at 14:35
29

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_start_job command from my stored procedure.

EXEC msdb.dbo.sp_start_job @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.

Improve this answer
6
  • 4
    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 '13 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 '13 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 '13 at 18:07
  • I liked the link from @Rusanu, but I wanted something with no response (which I think matches this problem). I wrote up my simplified version at abamacus.blogspot.com/2016/05/…
    – Abacus
    May 11 '16 at 21:09
  • Also if you try to start an SQL Agent job, it will fail with The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. Also neither Service Broker, or Sql Agent, exist on Azure. I don't know why Microsoft, after a decade and a half of people asking, refuse to add EXECUTE ASYNC RematerializeExpensiveCacheTable.
    – Ian Boyd
    Aug 2 '17 at 14:47
9

This old question deserves a more comprehensive answer. Some of these are mentioned in other answers/comments here, others may or may not work for OP's specific situation, but might work for others looking for calling stored procs asynchronously from SQL.

Just to be totally explicit: TSQL does not (by itself) have the ability to launch other TSQL operations asynchronously.

That doesn't mean you don't still have a lot of options:

  • SQL Agent jobs: Create multiple SQL jobs, and either schedule them to run at the time desired, or start them asynchronously from a "master control" stored proc using sp_start_job. If you need to monitor their progress programatically, just make sure the jobs each update a custom JOB_PROGRESS table (or you can check to see if they have finished yet using the undocumented function xp_sqlagent_enum_jobs as described in this excellent article by Gregory A. Larsen). You have to create as many separate jobs as you want parallel processes running, even if they are running the same stored proc with different parameters.
  • SSIS Package: For more complicated asynchronous scenarios, create an SSIS package with a simple branching task flow. SSIS will launch those tasks in individual spids, which SQL will execute in parallel. Call the SSIS package from a SQL agent job.
  • Custom application: Write a simple custom app in the language of your choice (C#, Powershell, etc), using the asynchronous methods provided by that language. Call a SQL stored proc on each application thread.
  • OLE Automation: In SQL, use sp_oacreate and sp_oamethod to launch a new process calling each other stored proc as described in this article, also by Gregory A. Larsen.
  • Service Broker: Look into using Service Broker, a good example of asynchronous execution in this article.
  • CLR Parallel Execution: Use the CLR commands Parallel_AddSql and Parallel_Execute as described in this article by Alan Kaplan (SQL2005+ only).
  • Scheduled Windows Tasks: Listed for completeness, but I'm not a fan of this option.

If it were me, I'd probably use multiple SQL Agent Jobs in simpler scenarios, and an SSIS package in more complex scenarios.

In your case, calling SQL Agent jobs sounds like a simple and manageable choice.

One final comment: SQL already attempts to parallelize individual operations whenever it can*. This means that running 2 tasks at the same time instead of after each other is no guarantee that it will finish sooner. Test carefully to see whether it actually improves anything or not.

We had a developer that created a DTS package to run 8 tasks at the same time. Unfortunately, it was only a 4-CPU server :)

*Assuming default settings. This can be modified by altering the server's Maximum Degree of Parallelism or Affinity Mask, or by using the MAXDOP query hint.

Improve this answer
3
  • Hi Brad. I have to believe you can start multiple sql agent jobs from a single sql agent job and they (the multiples) will run asynchronously. I believe the only criterion for success at the parent job level is that a sub job was started. Failure (I think) can only be seen by viewing the history of the sub jobs.
    – user8591443
    Nov 19 '19 at 18:40
  • @user8591443 That master job you're describing would have to use a SQL script that launched the other jobs via sp_start_job, as I describe in my first bullet point (since there is no built-in job step type of "start another job"). But I think you are correct about the individual job statuses not rolling up to the master; you'd have to query the failure/success status individually.
    – BradC
    Nov 19 '19 at 20:38
  • yes, each agent step (one for each job) in the master sql agent job would have a separate t-sql exec sp_start_job.
    – user8591443
    Nov 21 '19 at 17:35
8

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.

Improve this answer
6

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
Improve this answer
5
  • 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?
    – World Wide DBA
    Feb 18 '13 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 '13 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 '13 at 14:40
  • @Rachel - Yes. sp_start_job returns immediately. Can't remember what permissions it needs though.
    – Martin Smith
    Feb 18 '13 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 '13 at 15:02
1

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.

Improve this answer
2
  • 3
    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 '13 at 21:26
  • 1
    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 '13 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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