- [ @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.
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_idis int, with a default of NULL.
- [ @step_name = ] 'step_name'
The name of the step. step_nameis sysname, with no default.
- [ @subsystem = ] 'subsystem'
The subsystem used by the SQL Server Agent service to execute command. subsystem is nvarchar(40), and can be one of these values.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Operating-system command or executable program
Replication Distribution Agent job
Replication Snapshot Agent job
Replication Log Reader Agent job
Replication Merge Agent job
Replication Queue Reader Agent job
Analysis Services query (MDX, DMX).
Analysis Services command (XMLA).
Integration Services package execution
- [ @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.
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:
For more information about these tokens and updating your job steps to use the new token syntax, see Using Tokens in Job Steps.
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. codeis int, with a default of 0.
- [ @on_success_action= ] success_action
The action to perform if the step succeeds. success_actionis tinyint, and can be one of these values.
Quit with success
Quit with failure
Go to next step
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_actionis 4. success_step_idis int, with a default of 0.
- [ @on_fail_action= ] fail_action
The action to perform if the step fails. fail_actionis tinyint, and can be one of these values.
Quit with success
Quit with failure
Go to next step
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_actionis 4. fail_step_idis 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. Names that are enclosed in brackets ([ ]) are not allowed. 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. SQL Server Agent will include this parameter only if the job owner is a SQL Server sysadmin. If so, the given Transact-SQL step will be executed in the context of the given SQL Server user name. If the job owner is not a SQL Server sysadmin, then the Transact-SQL step will always be executed in the context of the login that owns this job, and the @database_user_name parameter will be ignored.
- [ @retry_attempts= ] retry_attempts
The number of retry attempts to use if this step fails. retry_attemptsis 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_intervalis int, with a default of 0, which indicates a 0-minute interval.
- [ @os_run_priority = ] run_priority
- [ @output_file_name= ] 'file_name'
The name of the file in which the output of this step is saved. file_nameis nvarchar(200), with a default of NULL. file_namecan include one or more of the tokens listed under command. This parameter is valid only with commands running on the Transact-SQL, CmdExec, PowerShell, Integration Services, or Analysis Services subsystems.
- [ @flags= ] flags
Is an option that controls behavior. flags is int, and can be one of these values.
Overwrite output file
Append to output file
Write Transact-SQL job step output to step history
Write log to table (overwrite existing history)
Write log to table (append to existing history)
Write all output to job history
Create a Windows event to use as a signal for the Cmd jobstep to abort
- [ @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.