sp_help_jobstep
Returns information for the steps in a job used by SQLServerAgent service to perform automated activities.
Syntax
sp_help_jobstep [ @job_id = ] 'job_id' |
[ @job_name = ] 'job_name'
[ , [ @step_id = ] step_id ]
[ , [ @step_name = ] 'step_name' ]
[ , [ @suffix = ] suffix ]
Arguments
[@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)
Result Sets
| 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. |
| server | sysname | Reserved. |
| 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. |
| os_run_priority | Int | Reserved. |
| 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. |
Permissions
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.
Examples
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