Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

sp_help_jobs_in_schedule (Transact-SQL)

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


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

The identifier of the schedule to list information for. schedule_id is int, with no default. Either schedule_id or schedule_name may be specified.

[ @schedule_name = ] 'schedule_name'

The name of the schedule to list information for. schedule_name is sysname, with no default. Either schedule_id or schedule_name may be specified.

0 (success) or 1 (failure)

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 to be executed.

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 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.

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

By default, members of the sysadmin fixed server role can execute this stored procedure. 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.

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.