Skip to content

sp-update-jobstep-transact-sql.md

Latest commit

 

History

History
193 lines (145 loc) · 9.14 KB

sp-update-jobstep-transact-sql.md

File metadata and controls

193 lines (145 loc) · 9.14 KB
title sp_update_jobstep (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service database-engine
ms.reviewer
ms.technology system-objects
ms.topic language-reference
f1_keywords
sp_update_jobstep
sp_update_jobstep_TSQL
dev_langs
TSQL
helpviewer_keywords
sp_update_jobstep
ms.assetid e158802c-c347-4a5d-bf75-c03e5ae56e6b
author stevestein
ms.author sstein

sp_update_jobstep (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

Changes the setting for a step in a job that is used to perform automated activities.

Topic link icon Transact-SQL Syntax Conventions

Syntax

  
sp_update_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 =] success_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 ]  
     [ ,  {   [ @proxy_id = ] proxy_id   
            | [ @proxy_name = ] 'proxy_name' }

Arguments

[ @job_id = ] job_id The identification number of the job to which the step belongs. job_idis uniqueidentifier, with a default of NULL. Either job_id or job_name must be specified but both cannot be specified.

[ @job_name = ] 'job_name' The name of the job to which the step belongs. job_nameis sysname, with a default of NULL. Either job_id or job_name must be specified but both cannot be specified.

[ @step_id = ] step_id The identification number for the job step to be modified. This number cannot be changed. step_idis int, with no default.

[ @step_name = ] 'step_name' Is a new name for the step. step_nameis sysname, with a default of NULL.

[ @subsystem = ] 'subsystem' The subsystem used by Microsoft SQL Server Agent to execute command. subsystem is nvarchar(40), with a default of NULL.

[ @command = ] 'command' The command(s) to be executed through subsystem. command is nvarchar(max), with a default of NULL.

[ @additional_parameters = ] 'parameters' [!INCLUDEssInternalOnly]

[ @cmdexec_success_code = ] success_code The value returned by a CmdExec subsystem command to indicate that command executed successfully. success_code is int, with a default of NULL.

[ @on_success_action = ] success_action The action to perform if the step succeeds.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 success_step_id.

[ @on_success_step_id = ] success_step_id The identification number of the step in this job to execute if step succeeds and success_action is 4. success_step_id is int, with a default of NULL.

[ @on_fail_action = ] fail_action The action to perform if the step fails. fail_action is tinyint, with a default of NULL 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 fail_step_id*.*

[ @on_fail_step_id = ] fail_step_id The identification number 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 NULL.

[ @server = ] 'server' [!INCLUDEssInternalOnly] server is nvarchar(128), with a default of NULL.

[ @database_name = ] 'database' The name of the database in which to execute a [!INCLUDEtsql] step. databaseis sysname. Names that are enclosed in brackets ([ ]) are not allowed. The default value is NULL.

[ @database_user_name = ] 'user' The name of the user account to use when executing a [!INCLUDEtsql] step. useris sysname, with a default of NULL.

[ @retry_attempts = ] retry_attempts The number of retry attempts to use if this step fails. retry_attemptsis int, with a default of NULL.

[ @retry_interval = ] retry_interval The amount of time in minutes between retry attempts. retry_interval is int, with a default of NULL.

[ @os_run_priority = ] run_priority [!INCLUDEssInternalOnly]

[ @output_file_name = ] 'file_name' The name of the file in which the output of this step is saved. file_name is nvarchar(200), with a default of NULL. This parameter is only valid with commands running in [!INCLUDEtsql] 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 cannot use the CHAR(32) function. For example, set this argument to an empty string as follows:

@output_file_name = ' '

[ @flags = ] flags 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 Transact-SQL job step output to step history
8 Write log to table (overwrite existing history)
16 Write log to table (append to existing history)

[ @proxy_id = ] proxy_id The ID number of the proxy that the job step runs as. proxy_id is type int, with a default of NULL. If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account for [!INCLUDEssNoVersion] Agent.

[ @proxy_name = ] 'proxy_name' The name of the proxy that the job step runs as. proxy_name is type sysname, with a default of NULL. If no proxy_id is specified, no proxy_name is specified, and no user_name is specified, the job step runs as the service account for [!INCLUDEssNoVersion] Agent.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_update_jobstep must be run from the msdb database.

Updating a job step increments the job version number.

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following [!INCLUDEssNoVersion] 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 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 [!INCLUDEssNoVersion] Agent service account for the proxy.

Examples

The following example changes the number of retry attempts for the first step of the Weekly Sales Data Backup job. After running this example, the number of retry attempts is 10.

USE msdb ;  
GO  
  
EXEC dbo.sp_update_jobstep  
    @job_name = N'Weekly Sales Data Backup',  
    @step_id = 1,  
    @retry_attempts = 10 ;  
GO

See Also

View or Modify Jobs
sp_delete_jobstep (Transact-SQL)
sp_help_jobstep (Transact-SQL)
System Stored Procedures (Transact-SQL)