Use This SP to Generate a SQL Server Job Report

By : Sekhar Mynam Jun 14, 2002

Printer friendly

Have you ever wanted to create a report listing all of the SQL Server jobs on your SQL Server 7.0 or 2000 servers? This is easy if you use the stored procedure I have created just for this purpose. The stored procedure, called sp_JobSchedule_rpt, creates this report by joining the sysjobs and sysjobschedules tables and creating a resultset.

To make the report easy to read, I have had to substitute the various integer values found in these tables with readable values. The first part of the stored procedure generates the list of jobs and the days to run for weekly scheduled jobs. The second part is written with CASE statements to incorporate all the possible combinations listed under the sysjobschedules table.

Making the Data Readable

The following are the various numeric entries and their string values, used in the stored procedure, to make it more readable.

freq_type int Frequency of the schedule execution:
1 = Once
4 = Daily
8 = Weekly
16 = Monthly
32 = Monthly relative
64 = Execute when SQL Server

freq_interval int Value indicating on which days the schedule runs.

If freq_type is 4 (daily), the value is every freq_interval days.

If freq_type is 8 (weekly), the value is a bitmask indicating the days in which weekly schedules are run. The freq_interval values are:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

If freq_type is 16 (monthly), the value is freq_interval day of the month.

If freq_type is 32 (monthly relative), freq_interval can be one of these values:
1 = Sunday 
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
8 = Day
9 = Weekday 
10 = Weekend day

freq_subday_type int Units for the freq_subday_interval:
1 = At the specified time
2 = Seconds
4 = Minutes
8 = Hours

freq_subday_interval int Number of freq_subday_type periods to occur between each scheduled execution of the job.

freq_relative_interval int Scheduled job’s occurrence of the freq_interval in each month when freq_type is 32 (monthly relative):
1 = First
2 = Second
4 = Third
8 = Fourth
16 = Last

freq_recurrence_factor int Number of weeks or months between the scheduled execution of the job.