While taking SQL Server Reporting Services (SSRS) for a trial run I came across a small disadvantage of the Reporting Services scheduler, which indirectly meant a small disadvantage of the SQL Scheduler. I had several clients that were possible SSRS recipients but the desired timelines were throwing a possible manual process into the mix. The central theme of the desired schedules centered around the closing of the accounting period which occurred on the 7th business day of the month versus the calendar day, which was what the SQL Scheduler natively supports. To alleviate this problem I put together a procedure that updates the schedule entries with the appropriate business day of the month, as well as a new table and helper functions.

Together, these objects are designed to update the calendar day entry of the SQL Agent schedule with the business day that was calculated from the passed date. The code is available for download here. To implement this it will be necessary to create a job that runs periodically and calls the procedure.

The code sample consists of the following:

1. Structure for the table to store the calendar days for each of the schedules that need to run on a specific business day
2. Data inserts for the table
3. Helper functions that provide the values to store in the system tables. NOTE: fnuGetWorkDay uses a table (HOLIDAY) that falls outside the scope of this script.
4. Procedure that updates the values

Here is an example call to the procedure to put in a job step:

EXEC dbo.spuSETFiscalSchedules @dt = ’12/1/2005′

Either the target database or the code will need some changes to get this working, but hopefully this provides a starting place for those interested.