| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_add_jobstep (Transact-SQL) |
sp_add_jobstep adds a step (operation) to a SQL Server Agent job. |
markingmyname |
maghan |
randolphwest |
08/21/2024 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current |
[!INCLUDE sql-asdbmi]
Adds a step (operation) to a [!INCLUDE ssnoversion-md] Agent job.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
Important
On Azure SQL Managed Instance, most, but not all [!INCLUDE ssnoversion-md] Agent job types are supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.
sp_add_jobstep
[ [ @job_id = ] 'job_id' ]
[ , [ @job_name = ] N'job_name' ]
[ , [ @step_id = ] step_id ]
, [ @step_name = ] N'step_name'
[ , [ @subsystem = ] N'subsystem' ]
[ , [ @command = ] N'command' ]
[ , [ @additional_parameters = ] N'additional_parameters' ]
[ , [ @cmdexec_success_code = ] cmdexec_success_code ]
[ , [ @on_success_action = ] on_success_action ]
[ , [ @on_success_step_id = ] on_success_step_id ]
[ , [ @on_fail_action = ] on_fail_action ]
[ , [ @on_fail_step_id = ] on_fail_step_id ]
[ , [ @server = ] N'server' ]
[ , [ @database_name = ] N'database_name' ]
[ , [ @database_user_name = ] N'database_user_name' ]
[ , [ @retry_attempts = ] retry_attempts ]
[ , [ @retry_interval = ] retry_interval ]
[ , [ @os_run_priority = ] os_run_priority ]
[ , [ @output_file_name = ] N'output_file_name' ]
[ , [ @flags = ] flags ]
[ , [ @proxy_id = ] proxy_id ]
[ , [ @proxy_name = ] N'proxy_name' ]
[ , [ @step_uid = ] 'step_uid' OUTPUT ]
[ ; ]
The identification number of the job to which to add the step. @job_id is uniqueidentifier, with a default of NULL.
Either @job_id or @job_name must be specified, but both can't be specified.
The name of the job to which to add the step. @job_name is sysname, with a default of NULL.
Either @job_id or @job_name must be specified, but both can't be specified.
The sequence identification number for the job step. @step_id is int, with a default of NULL. 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 isn't specified.
The name of the step. @step_name is sysname, with no default.
The subsystem used by the [!INCLUDE ssNoVersion] Agent service to execute @command. @subsystem is nvarchar(40), and can be one of these values.
| Value | Description |
|---|---|
ActiveScripting |
Active Script Important: [!INCLUDE ssNoteDepFutureAvoid] |
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 |
QueueReader |
Replication Queue Reader Agent job |
ANALYSISQUERY |
Analysis Services query (MDX, DMX) |
ANALYSISCOMMAND |
Analysis Services command (XMLA) |
SSIS |
[!INCLUDE ssISnoversion] package execution |
PowerShell |
PowerShell Script |
TSQL (default) |
[!INCLUDE tsql] statement |
The commands to be executed by the [!INCLUDE ssnoversion-md] Agent service through @subsystem. @command is nvarchar(max), with a default of NULL. [!INCLUDE ssnoversion-md] Agent provides token substitution, which gives you the same flexibility that variables provide when you write software programs.
An escape macro must accompany all tokens used in job steps, or else those job steps fail. In addition, you must now enclose token names in parentheses and place a dollar sign ($) at the beginning of the token syntax. For example: $(ESCAPE_<macro name>(DATE)).
For more information about these tokens and updating your job steps to use the new token syntax, see Use Tokens in Job Steps.
Any Windows user with write permissions on the Windows Event Log can access job steps that are activated by [!INCLUDE ssNoVersion] Agent alerts or WMI alerts. To avoid this security risk, [!INCLUDE ssNoVersion] Agent tokens that can be used in jobs activated by alerts are disabled by default. These tokens are: A-DBN, A-SVR, A-ERR, A-SEV, A-MSG, and WMI(<property>). In this release, use of tokens is extended to all alerting.
If you need to use these tokens, first ensure that only members of trusted Windows security groups, such as the Administrators group, have write permissions on the Event Log of the computer where [!INCLUDE ssNoVersion] resides. Then, right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens.
[!INCLUDE ssInternalOnly]
The value returned by a CmdExec subsystem command to indicate that @command executed successfully. @cmdexec_success_code is int, with a default of 0.
The action to perform if the step succeeds. @on_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 |
The ID of the step in this job to execute if the step succeeds and @on_success_action is 4. @on_success_step_id is int, with a default of 0.
The action to perform if the step fails. @on_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 |
The ID of the step in this job to execute if the step fails and @on_fail_action is 4. @on_fail_step_id is int, with a default of 0.
[!INCLUDE ssInternalOnly]
The name of the database in which to execute a [!INCLUDE tsql] step. @database_name is sysname, with a default of NULL, in which case the master database is used. Names that are enclosed in brackets ([]) aren't allowed. For an ActiveX job step, the @database_name is the name of the scripting language that the step uses.
The name of the user account to use when executing a [!INCLUDE tsql] step. @database_user_name is sysname, with a default of NULL. When @database_user_name is NULL, the step runs in the job owner's user context on @database_name. [!INCLUDE ssnoversion-md] Agent includes this parameter only if the job owner is a [!INCLUDE ssnoversion-md] sysadmin. If so, the given Transact-SQL step is executed in the context of the given [!INCLUDE ssnoversion-md] user name. If the job owner isn't a [!INCLUDE ssnoversion-md] sysadmin, then the Transact-SQL step is always executed in the context of the login that owns this job, and the @database_user_name parameter is ignored.
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.
The amount of time in minutes between retry attempts. @retry_interval is int, with a default of 0, which indicates a 0-minute interval.
[!INCLUDE ssinternalonly-md]
The name of the file in which the output of this step is saved. @output_file_name is nvarchar(200), with a default of NULL. @output_file_name can include one or more of the tokens listed under @command. This parameter is valid only with commands running on the [!INCLUDE tsql], CmdExec, PowerShell, [!INCLUDE ssISnoversion], or [!INCLUDE ssASnoversion] subsystems.
An option that controls behavior. @flags is int, and can be one of these values.
| Value | Description |
|---|---|
0 (default) |
Overwrite output file |
2 |
Append to output file |
4 |
Write [!INCLUDE tsql] job step output to step history |
8 |
Write log to table (overwrite existing history) |
16 |
Write log to table (append to existing history) |
32 |
Write all output to job history |
64 |
Create a Windows event to use as a signal for the cmd job step to abort |
The ID number of the proxy that the job step runs as. @proxy_id is int, with a default of NULL. If no @proxy_id is specified, no @proxy_name is specified, and no @database_user_name is specified, the job step runs as the service account for [!INCLUDE ssNoVersion] Agent.
The name of the proxy that the job step runs as. @proxy_name is sysname, with a default of NULL. If no @proxy_id is specified, no @proxy_name is specified, and no @database_user_name is specified, the job step runs as the service account for [!INCLUDE ssNoVersion] Agent.
@step_uid is an OUTPUT parameter of type uniqueidentifier.
0 (success) or 1 (failure).
None.
sp_add_jobstep must be run from the msdb database.
SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
By default, a job step runs as the service account for [!INCLUDE ssNoVersion] Agent unless another proxy is specified. A requirement of this account is to be a member of the sysadmin fixed security role.
A proxy might be identified by @proxy_name or @proxy_id.
This stored procedure shares the name of sp_add_jobstep with a similar object for the Azure Elastic Jobs service for Azure SQL Database. For information about the elastic jobs version, see jobs.sp_add_jobstep (Azure Elastic Jobs).
[!INCLUDE msdb-execute-permissions]
Other users must be granted one of the following [!INCLUDE ssNoVersion] Agent fixed database roles in the msdb database:
- SQLAgentUserRole
- SQLAgentReaderRole
- SQLAgentOperatorRole
For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.
The creator of the job step must have access to the proxy for the job step. Members of the sysadmin fixed server role have access to all proxies. Other users must be explicitly granted access to a proxy.
The following example creates a job step that changes database access to read-only for the Sales database. In addition, this example specifies five retry attempts, with each retry to occur after a 5-minute wait.
Note
This example assumes that the Weekly Sales Data Backup job already exists.
USE msdb;
GO
EXEC sp_add_jobstep
@job_name = N'Weekly Sales Data Backup',
@step_name = N'Set database to read only',
@subsystem = N'TSQL',
@command = N'ALTER DATABASE SALES SET READ_ONLY',
@retry_attempts = 5,
@retry_interval = 5;
GO