| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_update_jobstep (Transact-SQL) |
sp_update_jobstep changes the settings for a step in a job in the SQL Server Agent service. |
markingmyname |
maghan |
randolphwest, wiassaf |
01/07/2025 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current |
[!INCLUDE sql-asdbmi]
Changes the setting for a step in a job that is used to perform automated activities in the [!INCLUDE ssnoversion-md] Agent service.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_update_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' ]
[ ; ]
The identification number of the job to which the step belongs. @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 the step belongs. @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 identification number for the job step to be modified. @step_id is int, with no default. This number can't be changed.
A new name for the step. @step_name is sysname, with a default of NULL.
The subsystem used by [!INCLUDE ssnoversion-md] Agent to execute @command. @subsystem is nvarchar(40), with a default of NULL.
The commands to be executed through @subsystem. @command is nvarchar(max), with a default of NULL.
[!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 NULL.
The action to perform if the step succeeds. @on_success_action is tinyint, with a default of NULL, and can be one of these values.
| Value | Description (action) |
|---|---|
1 |
Quit with success |
2 |
Quit with failure |
3 |
Go to next step |
4 |
Go to step @on_success_step_id |
The identification number of the step in this job to execute if step succeeds and @on_success_action is 4. @on_success_step_id is int, with a default of NULL.
The action to perform if the step fails. @on_fail_action is tinyint, and can have one of these values.
| Value | Description (action) |
|---|---|
1 |
Quit with success |
2 |
Quit with failure |
3 |
Go to next step |
4 |
Go to step @on_fail_step_id |
The identification number 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 NULL.
[!INCLUDE ssInternalOnly]
The name of the database in which to execute a [!INCLUDE tsql] step. @database_name is sysname, with a default of NULL. Names that are enclosed in brackets ([]) aren't allowed.
The name of the database user to use when executing a [!INCLUDE tsql] step. @database_user_name is sysname, with a default of NULL.
The number of retry attempts to use if this step fails. @retry_attempts is int, with a default of NULL.
The amount of time in minutes between retry attempts. @retry_interval is int, with a default of NULL.
[!INCLUDE ssInternalOnly]
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. This parameter is only valid with commands running in [!INCLUDE tsql] or CmdExec subsystems.
To set @output_file_name back to NULL, you must set @output_file_name to an empty string, or to a string of blank characters, but you can't use the CHAR(32) function.
For example, set this argument to an empty string as follows:
@output_file_name = ' '
An option that controls behavior. @flags is int, with a default of NULL.
| Value | Description | Comment |
|---|---|---|
0 (default) |
Overwrite output file | |
2 |
Append to output file | |
4 |
Write Transact-SQL job step output to step history | Apply to Type: Transact-SQL, Analysis Services Command, and Analysis Query |
8 |
Write log to table (overwrite existing history) | |
16 |
Write log to table (append to existing history) | |
32 |
Write SSIS/CmdExe/PowerShell job step output to step history | Apply to Type: CmdExe, PowerShell, and Integration Services Package |
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.
0 (success) or 1 (failure).
sp_update_jobstep must be run from the msdb database.
Updating a job step increments the job version number.
This stored procedure shares the name of sp_update_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_update_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.
Only members of sysadmin can update a job step of a job owned by another user.
If the job step requires access to a proxy, the creator of the job step must have access to the proxy for the job step. All subsystems, except Transact-SQL, require a proxy account. Members of sysadmin have access to all proxies, and can use the [!INCLUDE ssNoVersion] Agent service account for the proxy.
The following example changes the number of retry attempts for the first step of the Weekly Sales Data Backup job. After you run this example, the number of retry attempts is 10.
USE msdb;
GO
EXECUTE dbo.sp_update_jobstep
@job_name = N'Weekly Sales Data Backup',
@step_id = 1,
@retry_attempts = 10;
GO