sp_help_jobstep

Transact-SQL Reference

Transact-SQL Reference

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.
2 = Quit the job reporting failure.
3 = Go to the next step.
4 = Go to step.

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
1 = Succeeded
2 = Retry
3 = Canceled
5 = Unknown

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

See Also

sp_add_jobstep

sp_delete_jobstep

sp_help_job

sp_update_jobstep

System Stored Procedures