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 ;