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.
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. Querona 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#
Querona 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 qua_full_load_cached_view_copy ''Sales.dbo.SalesData''',
@retry_attempts = 5,
@retry_interval = 5 ;