sp_help_jobs_in_schedule (Transact-SQL)
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 = Never1 = When a job succeeds2 = When the job fails3 = 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 = Failed1 = Succeeded3 = Canceled5 = 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
This stored procedure is owned by the db_owner role. You can grant EXECUTE
permissions for any user, but these permissions may 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