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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
MechDevTeamAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.