Search code examples
sqlsql-serversql-server-2008stored-proceduresjobs

SQL Server - Executing Jobs via JobCall - What is considered 'On Success'

I'm trying to get an understanding on the difference between executing a a Job by saying

EXEC spMyProc1 versus EXEC msdb.dbo.sp_start_job N'Job - SpMyProc1'

Everything is being run though SQl Server Job Agent.
Here is an example:

I have a Job with the following steps:
Step 1: in the Command box it has EXEC dbo.spJob1 -- just running a SP

Step 2: has the same type of command EXEC dbo.spJob2 -- spJob2 dependson Job1 to finish

Step 3: has the following:

EXEC msdb.dbo.sp_start_job N'Job3'
EXEC msdb.dbo.sp_start_job N'Job4'

Inside Job3 is EXEC spJob3
Inside Job4 is EXEC spJob4

Step 4 - once again has EXEC dbo.spJob5

enter image description here

I want to make sure that each job completes before the next one starts.

My understanding is that since Step 1 is executing an actual SP, 'On Success' means that the SP has completed successfully. Therefore, Step 2 will only start once the SP in Step1 has completed.

On the other hand, when Step 3 starts, 'On Success' for this step means just to start those other jobs. It will not wait for the SPs in those jobs to finish. Once Job3 and Job4 have started, the Job Agent will move on to Step 4.

Is this correct?

Secondly, in Step 3, will it wait for Job3 to complete or will it just launch Job Job3 and move on to launching spJob4 and never wait for the code inside to complete?

I'm using SQL Sever 2005

Thank you for any help.

Solution

  • If you use EXEC spMyProc1 inside a job step, it would wait till the SP is executed and completed and if successful as per your condition,it will move to next step.If you just use EXEC msdb.dbo.sp_start_job N'Job - SpMyProc1', It will just initiate the Job-SPMyProc1 and move to next step.It won't wait till the SP inside this initiated job gets completed.

    So, your understanding is correct:

    Since Step 1 is executing an actual SP, 'On Success' means that the SP has completed successfully. Therefore, Step 2 will only start once the SP in Step1 has completed. On the other hand, when Step 3 starts, 'On Success' for this step means just to start those other jobs. It will not wait for the SPs in those jobs to finish. Once Job3 and Job4 have started, the Job Agent will move on to Step 4.