You can execute SQL Server Agent jobs from within T-SQL code. The procedure sp_start_job can do that.
That's all well and good but notice that it doesn't say "execute job"; it just says "start job". The command starts the execution of a job but has no interest in when it completes.
Sometimes, you need to be able to start a job and wait for it to complete before taking a following action.
In our free SDU Tools for developers and DBAs, we added a procedure ExecuteJobAndWaitForCompletion to help with this.
In the main image above, you can see its default action ie: execute a job and wait for it to complete.
We have some other optional parameters though. In the screenshot below, you can see a parameter for determining how long to wait for the job to start, how long to then wait for it to complete, and whether or not to output debugging information:
You can also see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here: