How to Add a SQL Step to a Sql Agent Job with TSQL

In this scenario I had a job with one lonely step in it, but needed to add a step prior to that in order to initialize some parameters.

As I could not be absolutely sure of the job name(s) that this would be eventually deployed to, I needed to create a script that would:

1./ Add a step prior to the current and ensure correct step order

2./ Accept the job name or id as a parameter

3./ Also supply a script that would reverse the above – a rollback script

To do this I utilised the sp_add_jobstep command.

sp_add_jobstep command

sp_addjobstep is the command that we will use for the basis of our script.

To create the script to do this, initially I ran through what I wanted to do within SSMS and then scripted it out.

The problem with doing this, is that it uses id numbers, this wouldn’t work properly in another environment, so I edited to use the job name instead.

USE [msdb]
GO
DECLARE @jobnametochange VARCHAR(50) = 'DBA Fire SQL job'

EXEC msdb.dbo.sp_add_jobstep @job_name = @jobnametochange, @step_name=N'initialize parameters', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--commands to run here', 
		@database_name=N'DBAFire_DB', 
		@flags=0
GO

This works pretty well, it adds the step as the first step in the job as we required.

To delete the job step, added in the example above, run the below.


USE [msdb]
GO
DECLARE @jobnametodelete VARCHAR(50) = 'DBA Fire SQL job' 

EXEC msdb.dbo.sp_delete_jobstep @job_name= @jobnametodelete, @step_id=1

That does a great job if you want to add a step preceeding an existing step, but what if you want to add one after?

How to add a second step to a SQL Agent Job

Easy just change the @step_id to 2, or the number after the one you want it to be placed after.

E.g

USE [msdb]
GO
DECLARE @jobnametochange VARCHAR(50) = 'DBA Fire SQL job'

EXEC msdb.dbo.sp_add_jobstep @job_name = @jobnametochange, @step_name=N'initialize parameters', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_fail_action=2, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--commands to run here', 
		@database_name=N'DBAFire_DB', 
		@flags=0
GO

Its always a good idea to go into the properties of the SQL job and check everything has worked as intended.

Important: Test the end result thoroughly

If you spend some of your time working on SQL Server make sure you save us in your favorites – it would be great to see you again!

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.