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' ]  /* Not supported */
          [ , [ @cmdexec_success_code = ] code ]      /* Not supported */
     [ , [ @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' ]                     /* Not supported */
     [ , [ @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 to add the step. job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] 'job_name'

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

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'

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

[ @subsystem = ] 'subsystem'

The subsystem used by the scheduler service to execute command. Only TSQL subsystem is supported. subsystem is nvarchar(40) with a default of "TSQL".

[ @command = ] 'command' The commands to be executed by the scheduler service through a subsystem. command is nvarchar(max), with a default of NULL.

[ @additional_parameters = ] 'parameters' -- Not supported

Identified for informational purposes only. Not supported. parameters is ntext, with a default of NULL.

[ @cmdexec_success_code = ] code -- Not supported

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

[ @on_success_action = ] success_action

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

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

[ @on_fail_action = ] fail_action

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

The ID of the step in this job to execute if the step fails and fail_actionis 4. fail_step_id is int, with a default of 0.

[ @server =] 'server' -- Not supported

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. server is nvarchar(30), 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, in which case the master database is used. 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. When user is NULL, the step runs in the job owner's user context on database. Lyftron will include this parameter only if the job owner is a sysadmin. If so, the given Transact-SQL step will be executed in the context of the given a user name. If the job owner is not a sysadmin, then the Transact-SQL step will always be executed in the context of the login that owns this job, and the @database_user_name parameter will be ignored.

[ @retry_attempts = ] retry_attempts

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

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 -- Not supported

Reserved.

[ @output_file_name = ] 'file_name' -- Not supported

The name of the file in which the output of this step is saved. Not supported. file_name is nvarchar(200), with a default of NULL.

[ @flags = ] flags -- Not supported

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

[ @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.

[ @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.

Result Sets

None

Remarks

Lyftron Administrative Portal provides an easy, graphical way to manage jobs, and is the recommended way to create and manage jobs.

Permissions

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

The creator of the job step must have access to the proxy for the job step.

Examples

The following example creates a job step that changes database access to read-only for the Sales database. In addition, this example specifies 5 retry attempts, with each retry to occur after a 5 minute wait.

Note: This example assumes that the Weekly Sales Cache Rebuild job already exists.

EXEC sp_add_jobstep
    @job_name = N'Weekly Sales Cache Rebuild',
    @step_name = N'Rebuild weekely sales data cache',
    @subsystem = N'TSQL',
    @command = N'EXEC lyft_full_load_cached_view_copy ''Sales.dbo.SalesData''',
    @retry_attempts = 5,
    @retry_interval = 5 ;

See Also

• sp_add_job
• sp_add_schedule
• sp_delete_jobstep
• sp_update_jobstep