We help IT Professionals succeed at work.

MS SQL 2000 Expected Job run schedule

MechDevTeam asked
Does anyone have a query that will use the sysJobSchedules table in MS SQL 2000 to create a result set that will give me all the expected run times of a job for a given date range?

For example if a job is scheduled to run daily between 8am - 6pm  every hour i would recieve a result set as follows:

Name            RunTime
Job1            01/29/2008 08:00:00
Job1            01/29/2008 09:00:00
Job1            01/29/2008 10:00:00
Job1            01/29/2008 11:00:00
Job1            01/29/2008 12:00:00

The idea here is I'm trying to make a report that I can provide a from and to date range and return a gant chart type result to the user.  I figured I would ask on here if anyones found or written a query like this before I go and write one.
Watch Question

Give this a shot:

      J.name AS JobName,
      CASE WHEN JS.next_run_date = 0 OR JS.next_run_time = 0 THEN
                  CONVERT(VARCHAR(10), JS.next_run_date, 101)
                  + ' '
                  + STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(JS.next_run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
            AS DATETIME)
      END AS RunTime
      msdb..sysjobs J INNER JOIN msdb..sysJobSchedules JS
            ON J.job_id = JS.job_id
      J.enabled = 1
      AND JS.enabled=0


This only gives me the next runtime of the job not the scheduled run "TIMES" for a given date range.
My apologizes, I mis-read your original question....
Do you have a maximum range you would need to search/filter on, like up to on week in the future/past or unlimited?


Yes a week in future and past is probably the limit.  The problem is the only seed date you can take as a reference would be the "next_run_date" and "next_run_time".  

I don't know that this can be done in a single query as I need to turn multiple jobs with different schedule setups and turn it into multiple records using those job parameters.  

I guess I was looking for anyone who might have already written this type of app rather then me writing it.
Well, I think you are looking for a query that hasn't exactly been done before... I did find an article with some interesting information, but still not returning a dataset as clear as you are looking for... http://www.sql-server-performance.com/articles/dba/job_report_p1.aspx
Ya I can't find anyone that will post there query logic if they have actually written in.  

I was able to find a product to do what i'm wanting to do without having to write the program.  

For people checking this thread here are 2 products for your evaulation.


SQL Sentry seems much more robust but seems to cost a bit mor ethen the alternative.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.