Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a number of SQL Server Agent jobs will different types of schedules (Daily, Weekly, Monthly). Each of these jobs will represent an event. For example there will be an FTP job that will transfer a file from a source and destination location every day at 7:00AM. I am trying to build a web interface into this schedule so that I can not only view job history (this can be queried rather easily using the msdb..sysjobhistory table) but also view future scheduled runs.

Therefore, I am looking to pass in a job_id and date into a stored procedures and determine if that SQL Server Agent job will run on that day. For example, If I have a job that runs every second tuesday of every month, I want the stored procedure to return 1 if I pass in 12/9/2013 (2nd tuesday of december) and -1 if I pass 12/10/2013.

I know i can get the next_schedule_run date and time from msdb.dbo.sysjobschedules but need a way to extend this.

share|improve this question
    
You have to compute it yourself from the schedule pattern(s). –  Jon Seigel Nov 14 '13 at 17:48
1  
Note that the sysjobschedules table refreshes every 20 min, so NextRunDate may be out of date. Also, you can use the script from sqlfool.com/2012/09/sql-agent-job-script-2 and modify it as per your needs. –  Kin Nov 14 '13 at 17:49
    
This blog posts has exactly what im am looking for: weblogs.sqlteam.com/peterl/archive/2008/10/10/… –  user327999 Nov 14 '13 at 20:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.