TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sp_help_jobs_in_schedule (Transact-SQL)

 

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
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 nameData typeDescription
job_iduniqueidentifierUnique ID of the job.
originating_servernvarchar(30)Name of the server from which the job came.
namesysnameName of the job.
enabledtinyintIndicates whether the job is enabled to be executed.
descriptionnvarchar(512)Description for the job.
start_step_idintID of the step in the job where execution should begin.
categorysysnameJob category.
ownersysnameJob owner.
notify_level_eventlogintBitmask 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_emailintBitmask 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_netsendintBitmask 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_pageintBitmask 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_operatorsysnameE-mail name of the operator to notify.
notify_netsend_operatorsysnameName of the computer or user used when sending network messages.
notify_page_operatorsysnameName of the computer or user used when sending a page.
delete_levelintBitmask indicating under what circumstances the job should be deleted when a job completes. Possible values are the same as for notify_level_eventlog.
date_createddatetimeDate the job was created.
date_modifieddatetimeDate the job was last modified.
version_numberintVersion of the job (automatically updated each time the job is modified).
last_run_dateintDate the job last started execution.
last_run_timeintTime the job last started execution.
last_run_outcomeintOutcome of the job the last time it ran:

 0 = Failed

 1 = Succeeded

 3 = Canceled

 5 = Unknown
next_run_dateintDate the job is scheduled to run next.
next_run_timeintTime the job is scheduled to run next.
next_run_schedule_idintIdentification number of the next run schedule.
current_execution_statusintCurrent execution status.
current_execution_stepsysnameCurrent execution step in the job.
current_retry_attemptintIf the job is running and the step has been retried, this is the current retry attempt.
has_stepintNumber of job steps the job has.
has_scheduleintNumber of job schedules the job has.
has_targetintNumber of target servers the job has.
typeintType 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  

SQL Server Agent Stored Procedures (Transact-SQL)
sp_add_schedule (Transact-SQL)
sp_attach_schedule (Transact-SQL)
sp_delete_schedule (Transact-SQL)
sp_detach_schedule (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft