sp-help-jobstep-transact-sql.md

title	sp_help_jobstep (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	sp_help_jobstep_TSQL sp_help_jobstep
dev_langs	TSQL
helpviewer_keywords	sp_help_jobstep
ms.assetid	4a13b804-45f2-4f82-987f-42d9a57dd6db
caps.latest.revision	40
author	JennieHubbard
ms.author	jhubbard
manager	jhubbard

sp_help_jobstep (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

Returns information for the steps in a job used by [!INCLUDEssNoVersion] Agent service to perform automated activities.

Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version).

Transact-SQL Syntax Conventions

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'
The job identification number for which to return job information. job_id is uniqueidentifier, with a default of NULL.

[ @job_name =] 'job_name'
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
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'
The name of the step in the job. step_name is sysname, with a default of NULL.

[ @suffix =] suffix
A flag indicating whether a text description is appended to the flags column in the output. suffixis 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(max)	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	tinyint	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 [!INCLUDEtsql] step, this is the database in which the command executes.
database_user_name	sysname	For a [!INCLUDEtsql] 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 ([!INCLUDEtsql], CmdExec, and PowerShell 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.
proxy_id	int	Proxy for the job step.

Remarks

sp_help_jobstep is in the msdb database.

Permissions

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following [!INCLUDEssNoVersion] Agent fixed database roles in the msdb database:

• SQLAgentUserRole

• SQLAgentReaderRole

• SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Members of SQLAgentUserRole can only view job steps for jobs that they own.

Examples

A. Return information for all steps in a specific job

The following example returns all the job steps for the job named Weekly Sales Data Backup.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_jobstep  
    @job_name = N'Weekly Sales Data Backup' ;  
GO  

B. Return information about a specific job step

The following example returns information about the first job step for the job named Weekly Sales Data Backup.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_jobstep  
    @job_name = N'Weekly Sales Data Backup',  
    @step_id = 1 ;  
GO  

See Also

sp_add_jobstep (Transact-SQL)
sp_delete_jobstep (Transact-SQL)
sp_help_job (Transact-SQL)
sp_update_jobstep (Transact-SQL)
System Stored Procedures (Transact-SQL)