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 ;