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.
| Value | Description (action) |
|---|---|
| 1 | (default) Quit with success |
| 2 | Quit with failure |
| 3 | Go to next step |
| 4 | Go to step on_success_step_id |
[ @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.
| Value | Description (action) |
|---|---|
| 1 | Quit with success |
| 2 | (default) Quit with failure |
| 3 | Go to next step |
| 4 | Go to step on_fail_step_id |
[ @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 ;