sp_update_jobstep

Changes the setting for a step in a job that is used to perform automated activities.

Syntax

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 ]    /* not supported */
   [ , [@output_file_name =] 'file_name' ]    /* not supported */
   [ , [@flags =] flags ]                     /* not supported */
   [ ,  {   [ @proxy_id = ] proxy_id          /* not supported */
          | [ @proxy_name = ] 'proxy_name' }  /* not supported */

Arguments

[ @job_id =] job_id

The identification number of the job to which the step belongs. job_id is uniqueidentifier, with a default of NULL. Either job_id or job_name must be specified but both cannot be specified.

[ @job_name =] ‘job_name’

The name of the job to which the step belongs. job_name is sysname, with a default of NULL. Either job_id or job_name must be specified but both cannot be specified.

[ @step_id =] step_id

The identification number for the job step to be modified. This number cannot be changed. step_id is int, with no default.

[ @step_name =] ‘step_name’

Is a new name for the step. step_name is sysname, with a default of NULL.

[ @subsystem =] ‘subsystem’

Only Transact-SQL subsystem is supported to execute commands. subsystem is nvarchar(40), with a default of NULL.

[ @command =] ‘command’

The command(s) to be executed. command is nvarchar(max), with a default of NULL.

[ @additional_parameters =] ‘parameters’

Identified for informational purposes only. Not supported.

[ @cmdexec_success_code =] success_code

The value returned by a command to indicate that command executed successfully. success_code is int, with a default of NULL.

[ @on_success_action =] success_action

The action to perform if the step succeeds. success_action is tinyint, with a default of NULL, and can be one of these values.

Value Description (action)
1 Quit with success.
2 Quit with failure.
3 Go to next step.
4 Go to step success_step_id.

[ @on_success_step_id =] success_step_id

The identification number of the step in this job to execute if step succeeds and success_action is 4. success_step_id is int, with a default of NULL.

[ @on_fail_action =] fail_action

The action to perform if the step fails. fail_action is tinyint, with a default of NULL and can have one of these values.

Value Description (action)
1 Quit with success.
2 Quit with failure.
3 Go to next step.
4 Go to step fail_step_id.

[ @on_fail_step_id =] fail_step_id

The identification number of the step in this job to execute if the step fails and fail_action is 4. fail_step_id is int, with a default of NULL.

[ @server =] ‘server’

Identified for informational purposes only. Not supported. server is nvarchar(128), with a default of NULL.

[ @database_name =] ‘database’

The name of the database in which to execute a Transact-SQL step. database is sysname, with a default of NULL. Names that are enclosed in brackets ([ ]) are not allowed.

[ @database_user_name =] ‘user’

The name of the user account to use when executing a Transact-SQL step. user is sysname, with a default of NULL.

[ @retry_attempts =] retry_attempts

The number of retry attempts to use if this step fails. retry_attempts is int, with a default of NULL.

[ @retry_interval =] retry_interval

The amount of time in minutes between retry attempts. retry_interval is int, with a default of NULL.

[ @os_run_priority =] run_priority – not supported

Identified for informational purposes only. Not supported.

[ @output_file_name =] ‘file_name’ – not supported

The name of the file in which the output of this step is saved. file_name is nvarchar(200), with a default of NULL. This parameter is only valid with commands running in Transact-SQL subsystems.

To set output_file_name back to NULL, you must set output_file_name to an empty string (‘ ‘) or to a string of blank characters, but you cannot use the CHAR(32) function. For example, set this argument to an empty string as follows:

@output_file_name = ‘ ‘

[ @flags =] flags – not supported

An option that controls behavior. Not supported. flags is int, and can be one of these values.

Value Description
0 (default) Overwrite output file.
2 Append to output file.
4 Write Transact-SQL job step output to step history.
8 Write log to table (overwrite existing history).
16 Write log to table (append to existing history).

[ @proxy_id = ] proxy_id – not supported

The ID number of the proxy that the job step runs as. Not supported. _proxy_id_ is type int, with a default of NULL. If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account of Querona service.

[ @proxy_name = ] ‘proxy_name’ – not supported

The name of the proxy that the job step runs as. Not supported. proxy_name is type sysname, with a default of NULL. If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account of Querona service.

Remarks

Updating a job step increments the job version number.

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure.

Only members of sysadmin can update a job step owned by another user.

Examples

The following example changes the number of retry attempts for the first step of the Weekly Sales Cache Rebuild job. After running this example, the number of retry attempts is 10.

EXEC dbo.sp_update_jobstep
    @job_name = N'Weekly Sales Cache Rebuild',
    @step_id = 1,
    @retry_attempts = 10 ;

See Also