As a test I've created a job with 2 steps. The first step adds a new jobstep as step 2, the added step waits for 10 seconds and then quits the job reporting success. The next step generates an error, but after the new step is added this should never be run.
Looking into the job history, what actually happens is the new job step is added successfully as step 2, and the name of step 2 is the name of the added step, but step 2 generates the error that step 3 should at this point, even though step 3 should never be run.
Essentially it runs as if step 2 isn't there. In the job history step 2 is called the correct step name, but the code that is run is actually step 3.
73 Answers
I think there's confusion about how the job actually runs.
When you call a job, it will be run in entirety as it exists at the moment you call it. If you make changes to the job as part of the run, they will not be executed until the next time you run the job (you can easily verify this by adding an sp_delete_job step to delete the job early on and you can see that the job will still continue with the next steps even though it has been deleted).
So when you're calling the job initially, it is going to execute exactly what you have:
- Step 1: Create the new step 2
- Step 2: Throw an error
This is why you can't bypass your original second step by adding a new step to try to circumvent it. As far as SQL Server is concerned, the new step doesn't exist for this execution.
As for the name confusion - job history is not recorded until the process is complete and it logs the information using the INT step_id rather than the unique identifier for the step, which is why you see the new step name when you view the history even though the underlying logic that was executed is from your original step 2. Probably should be kicked up to MS to switch to using the UID to avoid these cases.
1Answering my own question in case anyone else has a similar situation to mine.
Other users suggested throwing an error, which would definitely work, however in my db all errors on scheduled jobs are logged and reviewed. The reason I want to stop the job is because I'd like to avoid running jobs on holidays, so throwing an error would cause unnecessary review.
What worked for me was to create a stored procedure that checks if it's a holiday. If it isn't a holiday the job continues as normal. If it is a holiday the job will use msdb.dbo.sp_stop_job to stop the job without an error. Some of these jobs run monthly, so I've also used sp_add_schedule and sp_attach_schedule to create and attach a one time run for the next day.
The [msdb].[dbo].[sysjobsteps] table is probably your friend here. Check it out especially on_success_action and on_fail_action columns. I would make sure steps preceeding the one you added have 3=onSuccessRunNextStep for their on_success_action.
This might be useful
DECLARE @MaxJobStep_id INT DECLARE @Job_id NVARCHAR(500) SELECT @Job_id= job_id FROM sysjobs WHERE name IN ('InsertJobNameHere') SELECT @MaxJobStep_id = MAX(Step_id) FROM sysjobsteps WHERE job_id = @Job_id EXEC sp_update_jobstep @job_name = 'InsertJobNameHere' , @step_id = @MaxJobStep_id , @on_success_action = 3 , @on_fail_action = 3 
