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.