Click here to Skip to main content
15,793,609 members
Articles / Programming Languages / T-SQL

Simple Dynamic Scheduler

Rate me:
Please Sign up or sign in to vote.
4.86/5 (20 votes)
23 May 2016CPOL7 min read 44.1K   2.7K   39   13
Simple, open-source, fully customizable, lightweight SQL Server scheduler
In this article, you will learn about the Simple Dynamic Scheduler, an alternative scheduling utility that uses SQL Server to store all configurations and logic.


Simple Dynamic Scheduler is an alternative scheduling utility which uses SQL Server to store all configurations and logic. The main characteristics of Simple Dynamic Scheduler are:

  • Simple - Easy to manage (once you finish reading this page)
  • Dynamic - Not static
  • Purely T-SQL - says it all
  • Open source - Adaptable code to suit your business needs
  • Light-weight - Provides only decision mechanism (Run or Hold) without actually executing anything

There are many schedulers today that can be used out-of-the-box, such as Windows Tasks, SQL Server Job Scheduler, 3rd party tolls, etc. But, consider the following situations:

  • You are unable to use any of the out-of-the-box schedulers.
  • You can use out-of-the-box schedulers, but it takes time and involves a lot of internal processes (waiting for OPS or DBA) to add/modify/remove/disable/enable schedulers.
  • It is not practical to create hundreds or thousands of scheduling tasks / jobs.
  • Your scheduler should change, depending on certain business rules.

If any of the situations presented above sound familiar, then Simple Dynamic Scheduler might be just the right solution for you.


Simple Dynamic Scheduler is built using SQL Server Scheduling Job as a reference. It provides you the ability to define:

  • Name of a scheduler
  • Schedule type (one time or recurring)
  • Schedule frequency (daily, weekly, monthly)
  • Recurrence (scheduler recurs every X days, weeks, months)
  • Days of week to run (for weekly schedules)
  • Exact date or weekday of the month to run (for monthly schedules)
  • Daily occurrence (once or multiple times per day)
  • Retention period of a scheduler (start and end date)
  • Active / Valid days (days to run schedule)
  • If scheduler is active of not (enabled or disabled)

All configuration parameters for all schedulers are stored in the configuration table. Only information that is not stored in the configuration table is last execution time of process which uses certain scheduler, because multiple processes can use same scheduler. Thus, you will need to maintain this information separately for your processes.

Code for Simple Dynamic Scheduler is pretty straightforward and consists of:

  • Table which contains schedule configurations (t_SimpleDynamicScheduler)
  • Scalar function which determines if certain scheduler should run or not (f_SimpleDynamicScheduler_CheckRun)
  • Table-value function which is used to simulate schedule run sequence (f_SimpleDynamicScheduler_Simulate)

Remember to follow this exact order of execution when creating database objects.

Types of Schedulers

Following types of schedulers can be created using Simple Dynamic Scheduler:

  • One time
  • Recurring
    • Daily
    • Weekly
    • Monthly
      • On the exact date
      • On the exact weekday

Additionally, all recurring schedulers may have intraday schedules (more than one schedule run per day). The following image shows columns in t_SimpleDynamicScheduler table that need to be populated depending on the type of the scheduler we want to create:

Image 1

Green fields are mandatory, yellow are optional and red are ignored. Using examples, we will show you how to create each of different types of schedulers.

Creating the Scheduler

As mentioned earlier, there are 5 different types of schedulers that can be defined using Simple Dynamic Scheduler. Using fictional scenarios, we will explain how to create each type of scheduler through an example. In scenario, we will underline important words and mark them with the subscript numbers. Later, we will use these subscript numbers to explain how information from scenario should be translated into the configuration table fields.

One-Time Scheduler

Let's assume we want to create one-time scheduler which will kick-off on 05 Apr 2016 at 10:30pm. Following fields should be initialized:

  • Name = My one-time scheduler (can be anything)
  • ¹Type = O
  • ³TimeStart = 22:30:00
  • ²StartDate = 2016-04-05
  • Enabled = True

Recurring, Daily Scheduler

If we want to create recurring daily scheduler which kicks of every three days once per day at 9:00am starting from 20 Apr 2016, ending on 31 May 2016, we would initialize the following fields:

  • Name = My daily recurring scheduler (can be anything)
  • ¹Type = R
  • ²Frequency = D
  • ³RecurseEvery = 3
  • DailyFrequency = O
  • TimeStart = 9:00:00
  • StartDate = 2016-04-20
  • EndDate = 2016-05-31 (inclusive)
  • Enabled = True

Recurring, Weekly Scheduler

Let's imagine we want to create recurring weekly scheduler which kicks off every second week on Monday, Thursday and Friday once per day at 2:30pm, starting from 15 Mar 2016:

  • Name = My weekly recurring scheduler (can be anything)
  • ¹Type = R
  • ²Frequency = W
  • ³RecurseEvery = 2
  • DaysOfWeek = Mo-Th-Fr
  • DailyFrequency = O
  • TimeStart = 14:30:00
  • StartDate = 2016-03-15
  • Enabled = True

Recurring, Monthly Scheduler on the Exact Date

In the next example, we will create recurring monthly scheduler which will kick off on 12th day of every third month, once per day at 11:00am starting from 10 Feb 2016₇ only on workdays:

  • Name = My monthly, on the date, recurring scheduler (can be anything)
  • ¹Type = R
  • ²Frequency = M
  • RecurseEvery = 3
  • ³MonthlyOccurrence = D
  • ³ExactDateOfMonth = 12
  • DailyFrequency = O
  • TimeStart = 11:00:00
  • StartDate = 2016-02-10
  • ValidDays = Mo-Tu-We-Th-Fr
  • Enabled = True

Recurring, Monthly Scheduler on the Exact Weekday

Similarly to previous scheduler, if we want to create recurring monthly scheduler which will kick off every second Sunday of each month once per day at 11:45pm starting from 1 Jan 2016ending on 31 Dec 2016:

  • Name = My monthly, on the weekday, recurring scheduler (can be anything)
  • ¹Type = R
  • ²Frequency = M
  • RecurseEvery = 1
  • MonthlyOccurrence = W
  • ExactWeekdayOfMonth = Su
  • ³ExactWeekdayOfMonthEvery = 2
  • DailyFrequency = O
  • TimeStart = 23:45:00
  • StartDate = 2016-01-01
  • EndDate = 2016-12-31 (inclusive)
  • Enabled = True

Intraday Scheduler (Applies for all Recurring Schedulers)

Intraday Scheduler is used in case we want schedule to run multiple times per day. For example, let's say we want to execute scheduler multiple times per day starting from 9:00am ending at 5:00pm every two hours. The following fields should be initialized:

  • ¹DailyFrequency = E
  • ²TimeStart = 9:00:00
  • OccursEveryValue = 2
  • OccursEveryTimeUnit = H
  • ³TimeEnd = 17:00:00


  • ¹DailyFrequency = E
  • ²TimeStart = 9:00:00
  • OccursEveryValue = 120
  • OccursEveryTimeUnit = M
  • ³TimeEnd = 17:00:00

Checking if Schedule Should Run

To determine if certain schedule should run, we will use scalar function called [dbo].[f_SimpleDynamicScheduler_CheckRun]:

SELECT [dbo].[f_SimpleDynamicScheduler_CheckRun] (
     <@pScheduleId, int,>
    ,<@pLastRun, datetime,>
    ,<@pCurrentDate, datetime,>)

Parameters of this function are:

  • <a>@pScheduleId</a> - ID of a schedule (ID column from configuration table)
  • <a>@pLastRun</a> - Last time when your process / job / activity that uses this scheduler has been run
  • @pCurrentDate - Current date and time (usually getdate(), left as a parameter for testing and simulation purposes)

Function returns the following values:

  • RUN - process should run
  • HOLD - process should not run
  • Anything else - there is an error in function. Return value is actual text of an error

Depending on the return result, you should implement logic in your code to:

  1. Run necessary code if necessary
  2. Update LastRun value of your process if process successfully completed
  3. Present an error (if any) to a user

Schedule Simulator

For testing and simulation purposes, we provided you with a function called [dbo].[f_SimpleDynamicScheduler_Simulate]. It gives you the ability to test schedule execution cycles by simulating its usage in defined period of time.

Let's take the following schedule as an example:

  • ID = 3
  • Type = R
  • Frequency = D
  • RecurseEvery = 1
  • DailyFrequency = E
  • TimeStart = 9:00:00
  • OccursEveryValue = 120
  • OccursEveryTimeUnit = M
  • TimeEnd = 16:30:00
  • StartDate = 2016-04-25
  • EndDate = 2016-05-13
  • Enabled = True

To simulate this schedule, we will run the following piece of code:

FROM [dbo].[f_SimpleDynamicScheduler_Simulate] (3, '20160405', '20160520', 'M', 10, null)

Parameters of this function say: for schedule 3, simulate executions in period from 05-Apr-2016 until 20-May-2016 in 10 minute increments. Previous LastRun is not known (NULL).

The image below shows results given by the simulator:

Image 2


At this point, you should be familiar with how to create, initialize and use Simple Dynamic Scheduler. If there is still something unclear or I missed something, please feel free to ask and comment and I will make sure those points are better explained.

Enjoy using Simple Dynamic Scheduler!


  • April 2016 - Initial version
  • May 2016 - Added ValidDays parameter which controls what days are "valid" for a schedule. For example, with this new functionality, we can control if schedule can be run on weekend or not by setting valid only days from Monday to Friday.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Australia Australia
Business Intelligence Professional passionate about turning data into meaningful information and helping business grow and make better "data-driven" decisions.

Comments and Discussions

QuestionYearly recurrence Pin
Member 240749530-Jul-20 17:05
Member 240749530-Jul-20 17:05 
QuestionInfinite loop with Intraday schedule Pin
Member 126777379-Aug-16 12:13
Member 126777379-Aug-16 12:13 
AnswerRe: Infinite loop with Intraday schedule Pin
Miljan Radovic10-Aug-16 3:01
Miljan Radovic10-Aug-16 3:01 
QuestionPurpose Pin
Keradinides11-May-16 1:21
professionalKeradinides11-May-16 1:21 
AnswerRe: Purpose Pin
Miljan Radovic11-May-16 4:25
Miljan Radovic11-May-16 4:25 
QuestionStill don't get it Pin
Member 111414275-May-16 13:07
Member 111414275-May-16 13:07 
AnswerRe: Still don't get it Pin
Miljan Radovic5-May-16 14:40
Miljan Radovic5-May-16 14:40 
GeneralFiles seem to be missing. Pin
Emmanouil Gimissis MSc ICSD5-May-16 3:35
professionalEmmanouil Gimissis MSc ICSD5-May-16 3:35 
GeneralRe: Files seem to be missing. Pin
Miljan Radovic5-May-16 4:26
Miljan Radovic5-May-16 4:26 
GeneralRe: Files seem to be missing. Pin
Emmanouil Gimissis MSc ICSD5-May-16 4:29
professionalEmmanouil Gimissis MSc ICSD5-May-16 4:29 
GeneralRe: Files seem to be missing. Pin
Miljan Radovic5-May-16 4:38
Miljan Radovic5-May-16 4:38 
QuestionIs there no GUI interface? Pin
Dougie the P3-May-16 14:44
professionalDougie the P3-May-16 14:44 
AnswerRe: Is there no GUI interface? Pin
Miljan Radovic3-May-16 16:56
Miljan Radovic3-May-16 16:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.