Returns information for the steps in a job used by SQLServerAgent service to perform automated activities.
sp_help_jobstep [ @job_id = ] 'job_id' |
[ @job_name = ] 'job_name'
[ , [ @step_id = ] step_id ]
[ , [ @step_name = ] 'step_name' ]
[ , [ @suffix = ] suffix ]
[@job_id =] 'job_id'
Is the job identification number for which to return job information. job_id is uniqueidentifier, with a default of NULL.
[@job_name =] 'job_name'
Is the name of the job. job_name is sysname, with a default NULL.
Note Either job_id or job_name must be specified, but both cannot be specified.
[@step_id =] step_id
Is the identification number of the step in the job. If not included, all steps in the job are included. step_id is int, with a default of NULL.
[@step_name =] 'step_name'
Is the name of the step in the job. step_name is sysname, with a default of NULL.
[@suffix =] suffix
Is a flag indicating whether a text description is appended to the flags column in the output. suffix is bit, with the default of 0. If suffix is 1, a description is appended.
Return Code Values
0 (success) or 1 (failure)
|Column name||Data type||Description|
|step_id||Int||Unique identifier for the step.|
|step_name||sysname||Name of the step in the job.|
|subsystem||nvarchar(40)||Subsystem in which to execute the step command.|
|command||nvarchar(3200)||Command executed in the step.|
|flags||Int||A bitmask of values that control step behavior.|
|cmdexec_success_code||Int||For a CmdExec step, this is the process exit code of a successful command.|
|on_success_action||timyint||Action to take if the step succeeds:
1 = Quit the job reporting success.
|on_success_step_id||Int||If on_success_action is 4, this indicates the next step to execute.|
|on_fail_action||Tinyint||What to do if the step fails. Values are same as on_success_action.|
|on_fail_step_id||Int||If on_fail_action is 4, this indicates the next step to execute.|
|database_name||sysname||For a Transact-SQL step, this is the database in which the command executes.|
|database_user_name||sysname||For a Transact-SQL step, this is the database user context in which the command executes.|
|retry_attempts||Int||Maximum number of times the command should be retried (if it is unsuccessful).|
|retry_interval||Int||Interval (in minutes) for any retry attempts.|
|output_file_name||nvarchar(200)||File to which command output should be written (Transact-SQL and CmdExec steps only).|
|last_run_outcome||Int||Outcome of the step the last time it ran:
0 = Failed
|last_run_duration||Int||Duration (in seconds) of the step the last time it ran.|
|last_run_retries||Int||Number of times the command was retried the last time the step ran.|
|last_run_date||Int||Date the step last started execution.|
|last_run_time||Int||Time the step last started execution.|
Execute permissions default to the public role. Anyone who can execute this procedure can also create, delete, or update a job, job step, job category, job schedule, job server, task, or job history information.
A. Return information for all steps in a specific job
This example returns all the job steps for a job named Backup Files.
USE msdb EXEC sp_help_jobstep @job_name = 'Backup Files'
B. Return information about a specific job step
This example returns information about the first job step for the job named Backup Files.
USE msdb EXEC sp_help_jobstep @job_name = 'Backup Files', @step_id = 1