MS SQL 2000 Expected Job run schedule

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.
MechDevTeamAsked:
Who is Participating?
 
MechDevTeamConnect With a Mentor Author Commented:
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.

http://www.sqlsentry.net/event-manager/standard-sql-server.asp
http://www.idera.com/Products/sqljobmanager/

SQL Sentry seems much more robust but seems to cost a bit mor ethen the alternative.
0
 
digital_thoughtsCommented:
Give this a shot:

SELECT
      J.name AS JobName,
      CASE WHEN JS.next_run_date = 0 OR JS.next_run_time = 0 THEN
            GETDATE()
      ELSE
            CAST(
                  (
                  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
FROM
      msdb..sysjobs J INNER JOIN msdb..sysJobSchedules JS
            ON J.job_id = JS.job_id
WHERE
      J.enabled = 1
      AND JS.enabled=0
0
 
MechDevTeamAuthor Commented:
This only gives me the next runtime of the job not the scheduled run "TIMES" for a given date range.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
digital_thoughtsCommented:
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?
0
 
MechDevTeamAuthor Commented:
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.
0
 
digital_thoughtsCommented:
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
0
All Courses

From novice to tech pro — start learning today.