In SQL Server, you can use the sp_update_jobstep stored procedure to modify an existing job step of a SQL Server Agent job.

When you create a SQL Server Agent job, you create one or more job steps for that job. The sp_update_jobstep procedure allows you to update a specific job step independent of the actual job or any other job steps.

Example

In this example, we create a job with one job step, then we use sp_update_jobstep to modify the job step.

Create the Job

First, create the job:

USE msdb;  
GO  
EXEC sp_add_job  
    @job_name = N'BackupMoviesDB',
    @description = N'Backup the Movies database.',
    @category_name = 'Database Maintenance';
GO  
EXEC sp_add_jobstep  
    @job_name = N'BackupMoviesDB',  
    @step_name = N'Run the backup',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [Movies] TO DISK = N''C:\Backups\Movies.bak'' WITH CHECKSUM',
    @retry_attempts = 5,  
    @retry_interval = 5;
GO
EXEC sp_add_schedule 
    @schedule_name = N'Run_Sat_5AM',
    @freq_type = 8,
    @freq_interval = 64,
    @freq_recurrence_factor = 1,
    @active_start_time = 050000;
GO  
EXEC sp_attach_schedule  
   @job_name = N'BackupMoviesDB',  
   @schedule_name = N'Run_Sat_5AM';
GO  
EXEC sp_add_jobserver  
    @job_name = N'BackupMoviesDB';
GO

The above code uses five stored procedures, but the sp_add_jobstep procedure is the only one that contains the details that can be changed with sp_update_jobstep. The other procedures have their own matching “update” procedures (such as sp_update_job and sp_update_schedule).

So just to be clear, the following part is the part that we can update with the sp_update_jobstep procedure.

EXEC sp_add_jobstep  
    @job_name = N'BackupMoviesDB',  
    @step_name = N'Run the backup',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [Movies] TO DISK = N''C:\Backups\Movies.bak'' WITH CHECKSUM',
    @retry_attempts = 5,  
    @retry_interval = 5;

In this case we provided six arguments when creating the job. The procedure actually accepts a lot more than that, but this will do for our purposes.

Update the Job Step

We can now use sp_update_jobstep to modify the above job step.

EXEC sp_update_jobstep  
    @job_name = N'BackupMoviesDB',
    @step_id = 1,  
    @step_name = N'Run the backup',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [Movies] TO DISK = N''C:\Backups\Movies.bak'' WITH CHECKSUM',
    @retry_attempts = 10,  
    @retry_interval = 5;

The only thing this example does is increase the retry attempts from 5 to 10.

The sp_update_jobstep stored procedure changes only those settings for which parameter values are supplied. If a parameter is omitted, the current setting is retained.

Therefore, in this instance, we could have omitted the other arguments, like this:

EXEC sp_update_jobstep  
    @job_name = N'BackupMoviesDB',
    @step_id = 1,
    @retry_attempts = 10;

Check the Job

You can use sp_help_job to get information about the SQL Server Agent jobs in the system.

You can use it with or without parameters, but to get the job step details, you need to provide the job name or ID.

Like this:

EXEC sp_help_job 
	@job_name = 'BackupMoviesDB';

Here’s what the result looks like when executing it in SSMS:

Here’s an example of the code when using the ID:

EXEC sp_help_job 
	@job_id = '158D714B-6686-48FD-BB41-D35C6EE70AED';

When using the ID, you can omit the parameter name if you wish.

Example:

EXEC sp_help_job '158D714B-6686-48FD-BB41-D35C6EE70AED';

Full Syntax

The full syntax of sp_update_jobstep goes like this:

sp_update_jobstep   
     {   [@job_id =] job_id   
       | [@job_name =] 'job_name' } ,  
     [@step_id =] step_id  
     [ , [@step_name =] 'step_name' ]  
     [ , [@subsystem =] 'subsystem' ]   
     [ , [@command =] 'command' ]  
     [ , [@additional_parameters =] 'parameters' ]  
     [ , [@cmdexec_success_code =] success_code ]  
     [ , [@on_success_action =] success_action ]   
     [ , [@on_success_step_id =] success_step_id ]  
     [ , [@on_fail_action =] fail_action ]   
     [ , [@on_fail_step_id =] fail_step_id ]  
     [ , [@server =] 'server' ]   
     [ , [@database_name =] 'database' ]  
     [ , [@database_user_name =] 'user' ]   
     [ , [@retry_attempts =] retry_attempts ]  
     [ , [@retry_interval =] retry_interval ]   
     [ , [@os_run_priority =] run_priority ]  
     [ , [@output_file_name =] 'file_name' ]   
     [ , [@flags =] flags ]  
     [ ,  {   [ @proxy_id = ] proxy_id   
            | [ @proxy_name = ] 'proxy_name' }

See Microsoft’s documentation for sp_update_jobstep for an explanation of each parameter, as well as the values that each accepts.

Note that updating a job step increments the job version number.