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 ;