Every so often you run into situations where you need to schedule a stored procedure or SQL query to run at specific times, like a month-end process, batch processing, data archiving etc.

While some database engines like Microsoft SQL support job scheduling others don’t. I found that the problem with native schedulers in databases is that the scheduling itself is often very limited with only support to run the jobs on specific intervals, not specific times or vice versa. Whether your database supports job scheduling or not, let me show you how you can use Optitask to schedule SQL queries or Stored Procedures to execute at specific dates and time intervals.

First you need to create a task, then you need to add a SQL Task action and specify the database connection and the SQL Query to execute. In my example I’ll be executing a stored procedure called “MyStoredProcedureName” and pass the current short date as an input parameter. For long running queries or procedures make sure you set the Command Timeout to an appropriate value in seconds.

After your SQL Task action is setup create a new Scheduling Trigger to specify a schedule to execute the task.

For this task I specified a schedule to start at a specific date and to continue every two weeks on the same day and time.

Another popular feature that cannot always be done with native database schedulers is to handle errors when your Stored Procedure or SQL query fails. With Optitask you can specify “Error Actions” that will execute when your main task fail, in this case I specified an error action to send an Email to our administrator when the SQL task failed: