sp_update_jobstep (T-SQL)

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]
    [, [@output_file_name =] 'file_name']
    [, [@flags =] flags]

Arguments

[@job_id =] job_id

Is the identification number of the job to which the step belongs. job_id is uniqueidentifier, with a default of NULL.

[@job_name =] 'job_name'

Is the name of the job to which the step belongs. job_name is sysname, with a default of NULL.

Note Either job_id or job_name must be specified, but both cannot be specified.

[@step_id =] step_id

Is 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'

Is the subsystem used by SQL Server Agent to execute command. subsystem is nvarchar(40), with a default of NULL.

[@command =] 'command'

Is the command(s) to be executed through subsystem. command is nvarchar(3200), with a default of NULL.

[@additional_parameters =] 'parameters'

Reserved.

[@cmdexec_success_code =] success_code

Is the value returned by a CmdExec subsystem command to indicate that command executed successfully. success_code is int, with a default of NULL.

[@on_success_action =] success_action

Is 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

Is 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

Is 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

Is 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'

Reserved. server is nvarchar(30), with a default of NULL.

[@database_name =] 'database'

Is the name of the database in which to execute a TSQL step. database is sysname, with a default of NULL.

[@database_user_name =] 'user'

Is the name of the user account to use when executing a TSQL step. user is sysname, with a default of NULL.

[@retry_attempts =] retry_attempts

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

[@retry_interval =] retry_interval

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

[@os_run_priority =] run_priority

Reserved.

[@output_file_name =] 'file_name'

Is 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 TSQL or CmdExec subsystems.

[@flags =] flag

Is an option that controls behavior. flags is int, and can be one of these values.

 

Value	Description
2	Append to output file.
4	Overwrite output file.
0 (default)	No options set.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

sp_update_jobstep must be run from the msdb database.

Updating a job step increments the job's version number.

Permissions

Execute permissions default to the public role.

Examples

This example changes the name of step 4 of the Archive Tables job to Sales Detail.

USE msdb

EXEC sp_update_jobstep @job_name = 'Archive Tables', @step_id = 4,

    @step_name = 'Sales Detail'

See Also