Sp_add_jobstep (TRANSACT-SQL)

Sp_add_jobstep (TRANSACT-SQL)

Updated:April 14, 2006

Add a step to the 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 ] 
     [ , { [ @proxy_id = ] proxy_id 
         | [ @proxy_name = ] 'proxy_name' } ]

Parameters

[@ Job_id =] job_id

ID of the job to be added. The data type of job_id is uniqueidentifier. The default value is null.

[@ Job_name =] 'job _ name'

The name of the job to add. The data type of job_name is sysname, and the default value is null.

Note:
You must specify job_id or job_name, but not both.

[@ Step_id =] step_id

Sequence ID of the job step. The step ID starts from 1 and increases progressively without interruption. If you insert a step into an existing sequence, the serial number is automatically adjusted. If step_id is not specified, a value is provided. The data type of step_id is int, and the default value is null.

[@ Step_name =] 'step _ name'

The name of the step. The data type of step_name is sysname, with no default value.

[@ Subsystem =] 'subsystem'

The subsystem that Microsoft SQL Server proxy service uses to execute commands. The data type of subsystem is nvarchar (40). It can be one of the following values:

Value	Description
'Activescripting'	Activity script
'Cmdexec'	Operating system commands or executable programs
'Stribucket'	Copy distribution agent job
'Snapshot'	Copying a snapshot proxy job
'Logreader'	Copy a Log Reader Agent job
'Merge'	Copy and merge proxy jobs
'Queuerupload'	Copy a Queue Reader Agent job
'Analysisquery'	Analysis Services Query (MDX, DMX ).
'Analysiscommand'	Analysis Services Command (xmla ).
'Dts'	Integration Services Package execution
'Tsql' (default)	Transact-SQL statements

[@ Command =] 'command'

Commands executed by the SQLServerAgent service through subsystem. The data type of command is nvarchar (max), and the default value is null. The SQL Server Agent provides token replacement, which provides the same flexibility as variables when writing software programs.

Important:

In SQL Server 2005 Service Pack 1, the markup syntax of the SQL Server Agent job step has been changed. Therefore, all tokens used in the job steps must now be accompanied by a conversion macro. Otherwise, these job steps will fail. In addition, use square brackets to call the SQL Server syntax marked by the SQL Server 2000 proxy job step (for example,"[Date]") Has also been changed. Now, you must enclose the token name in parentheses and add the dollar sign ($). For example: $ (Escape _Macro name(Date ))

For more information about these tokens and update job steps to use the new token syntax, see use tags in job steps.

Security description:

Any Windows user with write permission on Windows Event Logs can access the job steps activated by the SQL Server proxy alert or WMI alert. To ensure security, by default, the SQL Server proxy token that can be used in an alarm-activated job is disabled. These tokens are: A-DBN, A-SVR, A-err, A-sev, A-MSG, and WMI (property ). If you need to use these tokens, first make sure that only members of a trusted Windows Security Group (such as the Administrators group) have the write permission for the event logs on the computer where SQL Server is installed. Right-click "SQL Server proxy" in the object Resource Manager and select "properties ", on the alert system page, select replace tokens for all alert job responses to enable these tokens.

[@ Additional_parameters =] 'parameters'

Retained. The data type of parameters is ntext, and the default value is null.

[@ Cmdexec_success_code =] Code

The value returned by the cmdexec sub-system command to indicate that the command has been successfully executed. The data type of the Code is int, and the default value is 0.

[@ On_success_action =] success_action

The operation performed when the step is successful. The data type of success_action is tinyint, which can be one of the following values:

Value	Description (Operation)
1 (default)	Exit after successful
2	Exit after failure
3	Go to the next step
4	Go to step on_success_step_id.

[@ On_success_step_id =] success_step_id

ID of the step to be executed in the job when the step is successful and the value of success_action is 4. The data type of success_step_id is int, and the default value is 0.

[@ On_fail_action =] fail_action

The operation performed when the step fails. The data type of fail_action is tinyint, which can be one of the following values:

Value	Description (Operation)
1	Exit after successful
2 (default)	Exit after failure
3	Go to the next step
4	Go to step on_fail_step_id.

[@ On_fail_step_id =] fail_step_id

ID of the step to be executed in the job if the value of fail_action is 4. The data type of fail_step_id is int, and the default value is 0.

[@ Server =] 'server'

Retained. The data type of the server is nvarchar (30), and the default value is null.

[@ Database_name =] 'database'

The name of the database in which the transact-SQL step is executed. The data type of the database is sysname. The default value is null. The master database is used. For Activex job steps, database is the name of the script language used in this step.

[@ Database_user_name =] 'user'

The name of the user account to be used to execute the transact-SQL step. The user data type is sysname, and the default value is null. If the user is null, this step runs in the context of the job owner user in the database.

[@ Retry_attempts =] retry_attempts

The number of retries when this step fails. The data type of retry_attempts is int. The default value is 0, indicating no retry.

[@ Retry_interval =] retry_interval

The interval (in minutes) between two retries ). The data type of retry_interval is int. The default value is 0, indicating that the Retry Interval is 0 minutes.

[@ OS _run_priority =] run_priority

Retained.

[@ Output_file_name =] 'file _ name'

Used to save the name of the file output by this step. The data type of file_name is nvarchar (200), and the default value is null. File_name can contain one or more tokens listed under command. This parameter is only valid for commands running on the transact-SQL or cmdexec subsystem.

[@ Flags =] flags

Option for controlling behavior. The data type of flags is int, which can be one of the following values:

Value	Description
0 (default)	Overwrite output file
2	Append to output file
4	Write the step output of a Transact-SQL job to the step history
8	Write logs to a table (overwrite existing history records)
16	Write logs to the table (append to existing history records)

[@ Proxy_id =] proxy_id

The ID of the agent when the job step is run as a proxy. The data type of proxy_id is int, and the default value is null. If proxy_id, proxy_name, and user_name are not specified, the job step runs as the service account of the SQL Server proxy.

[@ Proxy_name =] 'proxy _ name'

The name of the agent when the job step is run as a proxy. The data type of proxy_name is sysname, and the default value is null. If proxy_id, proxy_name, and user_name are not specified, the job step runs as the service account of the SQL Server proxy.

Remarks

Sp_add_jobstep must be run from the MSDB database.

SQL Server Management Studio provides a graphical and easy way to manage jobs. We recommend that you use this method to create and manage basic job structures.

Unless the job step creator is a member of the SysAdmin fixed security role, a proxy must be specified in the job step.

The proxy can be identified by proxy_name or proxy_id.

Return code value

0 (successful) or 1 (failed)

Result set

None

Permission

By default, only members of the SysAdmin fixed server role can execute this stored procedure. Other users must be granted one of the following permissions for the SQL Server proxy fixed database role in the MSDB database:

• Sqlagentuserrole
  
• Sqlagentreaderrole
  
• Sqlagentoperatorrole
  

For more information about the permissions of these roles, see SQL Server proxy fixed database roles.

The creator of a job step must have the right to access the agent of the job step. Members of the SysAdmin fixed server role can access all proxies. Other users must be explicitly authorized to access the proxy.

Example

In the following example, you can create a jobAdventureworksThe database access permission is changed to read-only. In addition, this example also specifies five retries. The interval between each Retry is 5 minutes.

Note:
This example assumes thatWeekly sales data backupJob.

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'exec sp_dboption ''sales'', ''read only'', ''true''', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO