sp_add_jobstep (T-SQL)

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']
    [,[@cmdexec_success_code =] code]
    [,[@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']
    [,[@database_name =] 'database']
    [,[@database_user_name =] 'user']
    [,[@retry_attempts =] retry_attempts]
    [,[@retry_interval =] retry_interval]
    [,[@os_run_priority =] run_priority]
    [,[@output_file_name =] 'file_name']
    [,[@flags =] flags]

Arguments

[@job_id =] job_id

Is 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'

Is 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]

Is 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'

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

[@subsystem =] 'subsystem'

Is the subsystem used by SQL Server Agent to execute command. subsystem is nvarchar(40), and can be one of these values.

 

Value	Description
‘ACTIVESCRIPTING’	Active Script
‘CMDEXEC’	Operating-system command or executable program
‘DISTRIBUTION’	Replication Distribution Agent job
‘SNAPSHOT’	Replication Snapshot Agent job
‘LOGREADER’	Replication Log Reader Agent job
‘MERGE’	Replication Merge Agent job
‘TSQL’ (default)	Transact-SQL statement

[@command =] 'command'

Is the command(s) to be executed by SQL Server Agent through subsystem. command is nvarchar(3200), with a default of NULL. command can include one or more of the following case-sensitive tokens (which are replaced at run time).

 

Value	Description
[A-DBN]	Database name. If the job is run by an alert, this token automatically replaces the version 6.5 [DBN] token during the conversion process.
[A-SVR]	Server name. If the job is run by an alert, this token automatically replaces the version 6.5 [SVR] token during the conversion process.
[A-ERR]	Error number. If this job is run by an alert, this token automatically replaces the version 6.5 [ERR] token during the conversion process.
[A-SEV]	Error severity. If the job is run by an alert, this token automatically replaces the version 6.5 [SEV] token during the conversion process.
[A-MSG]	Message text. If the job is run by an alert, this token automatically replaces the version 6.5 [MSG] token during the conversion process.
[DATE]	Current date (in YYYYMMDD format).
[JOBID]	Job ID.
[MACH]	Computer name.
[MSSA]	Master SQLServerAgent name.
[SQLDIR]	The directory in which SQL Server is installed. By default, this value is C:\Mssql7.
[STEPCT]	A count of the number of times this step has executed (excluding retires). Can be used by the step command to force termination of a multistep loop.
[STEPID]	Step ID.
[TIME]	Current time (in HHMMSS format).
[STRTTM]	The time (in HHMMSS format) that the job began executing.
[STRTDT]	The date (in YYYYMMDD format) that the job began executing.

[@additional_parameters =] 'parameters'

Reserved. parameters is ntext, with a default of NULL.

[@cmdexec_success_code =] code

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

[@on_success_action =] success_action

Is 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

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

[@on_fail_action =] fail_action

Is 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

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

[@server =] 'server'

Reserved. server is nvarchar(30), with a default of NULL.

[@database_name =] 'database'

Is the name of the database in which to execute a TSQL step. database is sysname, with a default of NULL, in which case the master database is used.

[@database_user_name =] 'user'

Is the name of the user account to use when executing a TSQL 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.

[@retry_attempts =] retry_attempts

Is 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

Is 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

Reserved.

[@output_file_name =] 'file_name'

Is the name of the file in which the output of this step is saved. file_name is nvarchar(200), with a default of NULL. file_name can include one or more of the tokens listed under command. This parameter is valid only with commands running on the TSQL or CmdExec subsystems.

[@flags =] flags

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

 

Value	Description
2	Append to output file.
4	Overwrite output file.
0 (default)	No options set.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

SQL Server Enterprise Manager provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

Permissions

Execute permissions default to the public role.

Examples

This example creates a job step that changes database access to read-only for a database named sales. In addition, this example specifies five retry attempts every 5 minutes.

Note This example assumes that the Weekly Sales Data Backup job already exists.

USE msdb

EXEC sp_add_jobstep @job_name = 'Weekly Sales Data Backup',
    @step_name = 'Set database to read only',
    @subsystem = 'TSQL',
    @command = 'exec sp_dboption ''sales'', ''read only'', ''true''',
    @retry_attempts = 5,
    @retry_interval = 5

See Also