sp_add_jobstep

Adds a step (operation) to a job.

Syntax

sp_add_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 = ] 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 to add the step. job_id is uniqueidentifier, with a default of NULL.

[@job_name =] 'job_name'

Is the name of the job to which to add the step. 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 sequence identification number for the job step. Step identification numbers start at 1 and increment without gaps. If a step is inserted in the existing sequence, the sequence numbers are adjusted automatically. A value is provided if step_id is not specified. step_id is int, with a default of NULL.

[@step_name =] 'step_name'

Is the name of the step. step_name is sysname, with no default.

[@subsystem =] 'subsystem'

Is the subsystem used by SQL Server Agent service to execute command. subsystem is nvarchar(40), and can be one of these values.

[@command =] 'command'

Is the command(s) to be executed by SQLServerAgent service through subsystem. command is nvarchar(3200), with a default of NULL. command can include one or more of the following case-sensitive tokens which are replaced at run time.

[@additional_parameters =] 'parameters'

Reserved. parameters is ntext, with a default of NULL.

[@cmdexec_success_code =] code

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

[@on_success_action =] success_action

Is the action to perform if the step succeeds. success_action is tinyint, and can be one of these values.

[@on_success_step_id =] success_step_id

Is the ID of the step in this job to execute if the step succeeds and success_action is 4. success_step_id is int, with a default of 0.

[@on_fail_action =] fail_action

Is the action to perform if the step fails. fail_action is tinyint, and can be one of these values.

[@on_fail_step_id =] fail_step_id

Is the ID 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 0.

[@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, in which case the master database is used.

[@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. When user is NULL, the step runs in the job owner's user context on database.

[@retry_attempts =] retry_attempts

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

[@retry_interval =] retry_interval

Is the amount of time in minutes between retry attempts. retry_interval is int, with a default of 0, which indicates a 0-minute interval.

[@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. file_name can include one or more of the tokens listed under command. This parameter is valid only with commands running on the TSQL or CmdExec subsystems.

[@flags =] flags

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

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

SQL Server Enterprise Manager provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

Permissions

Execute permissions default to the public role.

Examples

This example creates a job step that changes database access to read-only for a database named sales. In addition, this example specifies five retry attempts every 5 minutes.

Note  This example assumes that the Weekly Sales Data Backup job already exists.

USE msdb
EXEC sp_add_jobstep @job_name = 'Weekly Sales Data Backup',
   @step_name = 'Set database to read only',
   @subsystem = 'TSQL',
   @command = 'exec sp_dboption ''sales'', ''read only'', ''true''', 
   @retry_attempts = 5,
   @retry_interval = 5

See Also

Modifying and Viewing Jobs

sp_add_job

sp_add_jobschedule

sp_delete_jobstep

sp_help_job

sp_help_jobstep

sp_update_jobstep

System Stored Procedures