Export (0) Print
Expand All
Expand Minimize
7 out of 8 rated this helpful - Rate this topic

sp_add_jobstep (Transact-SQL)

Updated: 14 April 2006

Adds a step (operation) to a job.


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' } ]
[ @job_id = ] job_id

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'

The name of the job to which to add the step. job_name is sysname, with a default of NULL.

ms187358.note(en-US,SQL.90).gifNote:
Either job_id or job_name must be specified, but both cannot be specified.

[ @step_id = ] step_id

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'

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

[ @subsystem = ] 'subsystem'

The subsystem used by Microsoft SQL Server Agent service 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

'QueueReader'

Replication Queue Reader Agent job

'ANALYSISQUERY'

Analysis Services query (MDX, DMX).

'ANALYSISCOMMAND'

Analysis Services command (XMLA).

'Dts'

Integration Services package execution

'TSQL' (default)

Transact-SQL statement

[ @command = ] 'command'

The commands to be executed by SQLServerAgent service through subsystem. command is nvarchar(max), with a default of NULL. SQL Server Agent provides token substitution that gives you the same flexibility that variables provide when you write software programs.

ms187358.note(en-US,SQL.90).gifImportant:
In SQL Server 2005 Service Pack 1, the SQL Server Agent job step token syntax has changed. As a result, an escape macro must now accompany all tokens used in job steps, or else those job steps will fail. In addition, the SQL Server 2000 syntax, which used square brackets to call out SQL Server Agent job step tokens (for example, "[DATE]") has also changed. 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 Using Tokens in Job Steps.

ms187358.security(en-US,SQL.90).gifSecurity Note:
Any Windows user with write permissions on the Windows Event Log can access job steps that are activated by SQL Server Agent alerts or WMI alerts. To avoid this security risk, SQL Server 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).

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 SQL Server 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.

[ @additional_parameters = ] 'parameters'

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. parameters is ntext, with a default of NULL.

[ @cmdexec_success_code = ] code

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

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

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

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

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'

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. server is nvarchar(30), with a default of NULL.

[ @database_name = ] 'database'

The name of the database in which to execute a Transact-SQL step. database is sysname, with a default of NULL, in which case the master database is used. For an ActiveX job step, the database is the name of the scripting language that the step uses.

[ @database_user_name = ] 'user'

The name of the user account to use when executing a Transact-SQL 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

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

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

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @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. file_name can include one or more of the tokens listed under command. This parameter is valid only with commands running on the Transact-SQL or CmdExec subsystems.

[ @flags = ] flags

Is 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 SQL Server 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 SQL Server Agent.

0 (success) or 1 (failure)

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.

A job step must specify a proxy unless the creator of the job step is a member of the sysadmin fixed security role.

A proxy may be identified by proxy_name or proxy_id.

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server 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 AdventureWorks database. In addition, this example specifies 5 retry attempts, with each retry to occur after a 5 minute wait.

ms187358.note(en-US,SQL.90).gifNote:
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'exec sp_dboption ''sales'', ''read only'', ''true''', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO

Release History

14 April 2006

New content:
  • Inserted Important note about SQL Server Agent token syntax change in SQL Server 2005 Service Pack 1.
Changed content:
  • Removed descriptions of old SQL Server Agent token syntax.

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.