Edit

Share via

sp_help_jobs_in_schedule (Transact-SQL)

  • Article

Applies to: SQL Server

Returns information about the jobs that a particular schedule is attached to.

Transact-SQL syntax conventions

Syntax

sp_help_jobs_in_schedule
    [ [ @schedule_name = ] N'schedule_name' ]
    [ , [ @schedule_id = ] schedule_id ]
[ ; ]

Arguments

[ @schedule_id = ] schedule_id

The identifier of the schedule to list information for. @schedule_id is int, with a default of NULL.

Either @schedule_id or @schedule_name can be specified.

[ @schedule_name = ] N'schedule_name'

The name of the schedule to list information for. @schedule_name is sysname, with a default of NULL.

Either @schedule_id or @schedule_name can be specified.

Return code values

0 (success) or 1 (failure).

Result set

Returns the following result set:

Column name Data type Description
job_id uniqueidentifier Unique ID of the job.
originating_server nvarchar(30) Name of the server from which the job came.
name sysname Name of the job.
enabled tinyint Indicates whether the job is enabled, so that it can execute.
description nvarchar(512) Description for the job.
start_step_id int ID of the step in the job where execution should begin.
category sysname Job category.
owner sysname Job owner.
notify_level_eventlog int Bitmask indicating under what circumstances a notification event should be logged to the Microsoft Windows application log. Can be one of these values:

0 = Never
1 = When a job succeeds
2 = When the job fails
3 = Whenever the job completes (regardless of the job outcome)
notify_level_email int Bitmask indicating under what circumstances a notification e-mail should be sent when a job completes. Possible values are the same as for notify_level_eventlog.
notify_level_netsend int Bitmask indicating under what circumstances a network message should be sent when a job completes. Possible values are the same as for notify_level_eventlog.
notify_level_page int Bitmask indicating under what circumstances a page should be sent when a job completes. Possible values are the same as for notify_level_eventlog.
notify_email_operator sysname E-mail name of the operator to notify.
notify_netsend_operator sysname Name of the computer or user used when sending network messages.
notify_page_operator sysname Name of the computer or user used when sending a page.
delete_level int Bitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are the same as for notify_level_eventlog.
date_created datetime Date the job was created.
date_modified datetime Date the job was last modified.
version_number int Version of the job (automatically updated each time the job is modified).
last_run_date int Date the job last started execution.
last_run_time int Time the job last started execution.
last_run_outcome int Outcome of the job the last time it ran:

0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown
next_run_date int Date the job is scheduled to run next.
next_run_time int Time the job is scheduled to run next.
next_run_schedule_id int Identification number of the next run schedule.
current_execution_status int Current execution status.
current_execution_step sysname Current execution step in the job.
current_retry_attempt int If the job is running and the step has been retried, this value is the current retry attempt.
has_step int Number of job steps the job has.
has_schedule int Number of job schedules the job has.
has_target int Number of target servers the job has.
type int Type of the job:

1 = Local job.
2 = Multiserver job.
0 = Job has no target servers.

Remarks

This procedure lists information about jobs attached to the specified schedule.

Permissions

You can grant EXECUTE permissions on this procedure, but these permissions might be overridden during a SQL Server upgrade.

Other users must be granted one of the following SQL Server 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 the status of jobs that they own.

Examples

The following example lists the jobs attached to the NightlyJobs schedule.

USE msdb;
GO

EXEC sp_help_jobs_in_schedule
    @schedule_name = N'NightlyJobs';
GO

Related content