SQLTimer: a SQL Server Timer



So…you’ve got a fancy Business Intelligence (BI) solution? Predictive Analytics or perhaps Actionable Analytics? Ever thought about the right time to contact your (potential) customers?

SQLTimer might be a handy addition to your BI Suite!
Like a timer for your lighting, it checks every hour whether it’s the right moment to execute jobs for categories like:

BusinessHour: Indicates whether or not it’s the right time to contact your customer during business hours (included in the SQLTimer script)

Other additional time schedules which are downloadable:
Twitter: Indicates whether or not it’s the right time to contact your Twitter users
FaceBook: Indicates whether or not it’s the right time to contact your FaceBook users
LinkedIn: Indicates whether or not it’s the right time to contact your LinkedIn users
GooglePlus: Indicates whether or not it’s the right time to contact your Google+ users
Pinterest: Indicates whether or not it’s the right time to contact your Pinterest users

In this blogpost, I’ll be diving into the SQLTimer itself and the free time schedule called ‘BusinessHour’ (included in the script).

Download the complete script free on:
SQLTimer – How to install

How it works:
Just like the timer for your lightbulb, it is configured on an hourly basis:


The values indicate whether it is a bad, better or even the best time to contact your business on a weekly basis:

BusinessHour values:
0) = Outside of business hours
1) = Inside of business hours
2) = Preferred hours

Important note: SQLTimer is setup on a day-to-day basis and thus you might tweak the time schedule a bit for local holidays or other important days in the year:


SQLTimer objects:
(An existing database is required)
Let’s have a look at all the SQLTimer objects:


*** Tables ***

This table contains the logging (when a time schedule was executed (StartTime/EndTime) and what value the time schedule had)

SQLTimer’s logging in timer.TimerLogging:


This table actually contains the time schedule itself

This table is all about the configuration

SQLTimer’s configuration in timer.TimeScheduleConfiguration
In case you would like to change the name of the schema, don’t forget to change the configuration settings:


*** Stored Procedures ***

This SP is the timer itself and needs to be scheduled in SQL Agent to be run on an hourly basis

You might want to use the timer.usp_Select_TimeSchedule() stored procedure to analyze the time schedule for the coming week:


This SP will execute the preferred jobs (for example: T-SQL code, SP’s or SQL Agent Jobs) for the time schedule ‘BusinessHour’

Here’s how the timer.usp_Execute_BusinessHour_Jobs looks like.
You need to add your T-SQL code, SP’s or SQL Agent Jobs here that need to be executed if the @TimeScheduleValue reaches a certain value.


*** Install the SQLTimer ***

So far about the SQLTimer itself, it’s time to install it!
1) Download the T-SQL script here: SQLTimer – How to install

2) Unzip the ZIP-file and open the *.sql file in SSMS or SSDT

3) Change the database name and uncomment the T-SQL code:


4) Execute the T-SQL script
Output of the script:


5) Create a SQL Agent Job (Execute T-SQL statements) which executes the timer.usp_Hourly_Timer stored procedure every hour

6) Don’t forget to update the time schedule with your local (company) holidays 🙂
Table: timer.TimeSchedule

BumbleBI IT


One thought on “SQLTimer: a SQL Server Timer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s