sp_help_jobschedule

Transact-SQL Reference

Transact-SQL Reference

sp_help_jobschedule

Returns information about the scheduling of jobs used by SQL Server Enterprise Manager to perform automated activities.

Syntax

sp_help_jobschedule
    [ @job_id =] job_id |
    [ @job_name = ] 'job_name'
    [ , [ @schedule_name = ] 'schedule_name' ] |
    [ , [ @schedule_id = ] schedule_id ]

Arguments

[@job_id =] job_id

Is the job identification number. job_id is uniqueidentifier, with a default of NULL.

[@job_name =] 'job_name'

Is the name of the job. job_name is varchar(100), with a default of NULL.

Note  Either job_id or job_name must be specified, but both cannot be specified.

[@schedule_name =] 'schedule_name'

Is the name of the schedule item for the job. schedule_name is varchar(100), with a default of NULL.

{[@schedule_id =] schedule_id

Is the identification number of the schedule item for the job. schedule_id is int, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets
Column name Data type Description
schedule_id int Schedule identifier number.
schedule_name sysname Name of the schedule.
enabled int Whether the schedule enabled (1) or not enabled (0).
freq_type int Value indicating when the job is to be executed.

1 = Once
4 = Daily
8 = Weekly
16 = Monthly
32 = Monthly, relative to the freq_ interval
64 = Run when SQLServerAgent service starts.

freq_interval int Days when the job is executed. The value depends on the value of freq_type. For more information, see sp_add_jobschedule.
freq_subday_type int Units for freq_subday_interval. For more information, see sp_add_jobschedule.
freq_subday_interval int Number of freq_subday_type periods to occur between each execution of the job. For more information, see sp_add_jobschedule.
freq_relative_interval int Scheduled job's occurrence of the freq_interval in each month. For more information, see sp_add_jobschedule.
freq_recurrence_factor int Number of months between the scheduled execution of the job.
active_start_date int Date the schedule is activated.
active_end_date int End date of the schedule.
active_start_time int Time of the day the schedule starts.
active_end_time int Time of the day schedule ends.
date_created datetime Date the schedule is created.
schedule_description nvarchar(4000) An English description of the schedule (if scheduled).
next_run_date int Date the schedule will next cause the job to run.
next_run_time int Time the schedule will next cause the job to run.

Remarks

The parameters of sp_help_jobschedule can be used only in certain combinations. If schedule_id is specified, neither job_id nor job_name can be specified. Otherwise, the job_id or job_name parameters can be used with schedule_name.

Permissions

Execute permissions default to the public role. Anyone who can execute this procedure can also create, delete, or update a job, job step, job category, job schedule, job server, task, or job history information.

Examples
A. Return the job schedule for a specific job

This example returns the scheduling information for a job named Archive Tables.

USE msdb
EXEC sp_help_jobhistory @job_name = 'Archive Tables'
B. Return the job schedule for a named item in the schedule

This example returns the history for a job named Archive Tables and for its schedule item Weekly Archive.

USE msdb
EXEC sp_help_jobhistory @job_name = 'Archive Tables', 
   @schedule_name = 'Weekly Archive'

See Also

sp_add_jobschedule

sp_delete_jobschedule

sp_update_jobschedule

System Stored Procedures