sp_help_jobstep (T-SQL)

Returns information for the steps in a job used by SQL Server Agent 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

sp_add_jobstep	sp_update_jobstep
sp_delete_jobstep	System Stored Procedures
sp_help_job