none
SQL Server Agent - Will Job Run on Specific Future Date?

    Question

  • 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.

    thanks in advance.


    • Edited by csalle2323 Thursday, November 14, 2013 4:15 PM
    • Moved by ArthurZMVP Thursday, November 14, 2013 4:50 PM
    Thursday, November 14, 2013 4:14 PM

Answers

  • This blog post outlines a stored procedure that does exactly what i am looking for:

    http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx

    • Marked as answer by csalle2323 Thursday, November 14, 2013 8:44 PM
    Thursday, November 14, 2013 8:44 PM