Problem solve Get help with specific problems with your technologies, process and projects.

Scheduling with SQL

If you want a tip on how to schedule recurring tasks on MS SQL Server, read this tip.

As developers or DBA's we often have a need to make something happen on a recurring basis. One example might be running scheduled jobs or tasks. Many database management systems, such as Microsoft SQL Server, have built-in facilities by which you can schedule a job to reoccur.

Scheduling recurring tasks is elegantly automated by SQL Server's EM user interface. Behind the scenes, SQL Server stores scheduling data in a MSDB database. If you wish to incorporate similar functionality into your applications, you can call SQL Server-supplied system procedures from your own code. Before you can do this, you need to have a good understanding of the code SQL Server runs when you schedule jobs.

First, let's go ahead and have a look at what the SQL Server UI does for us behind the scenes when we schedule a job (named 'test') to reoccur every two weeks on Tuesdays and Thursdays starting today and ending 1/1/02. All we have to do is set up a job that runs according to this schedule and then script it. Listing 1 (see below) shows one way to do this using SQL Server version 2000. (I have added some comments to clarify what SQL Server is doing.)

So what happened? SQL Server executed a system stored procedure called SP_ADD_JOBSCHEDULE within the MSDB database in order to schedule this job. Feel free to have a look at that system procedure. Notice that this procedure supplies a default end date of 12/31/9999. Indeed, it is important to have an end date; otherwise the jobs we schedule will run till the end of time! SQL Server adds appropriate entries to SYSJOBSCHEDULES for recurrence frequency, recurrence interval, etc. It'll then query this table and determine whether it is time to run the task.

In this article, I also propose an approach that might be useful in your applications. Instead of polling SQL Server and determining whether it's appropriate to run a particular job, why don't we make up a schedule for each job as soon as it is scheduled? In other words, let's make up a table that contains a job ID, as well as date and time of all of its occurrences. Then you could write a stored procedure that runs daily (or however often you want to run it) and checks the jobs that are to occur on each particular day. For each of these tasks, you will also need a stored procedure that executes them or takes the appropriate action. Let's keep in mind that we're talking about all kinds of tasks, not just SQL Server jobs.

So how might you apply this trick? Suppose your local SQL Server user group meets on the second Monday of every month. You can have the stored procedure run on the first day of the month, so that one week before the meeting, SQL Server will automatically send an email to all the members of the group, or activate an application that prints flyers or address labels. Cool, huh?


Listing 1: T-SQL Code for scheduling jobs in SQL Server.

BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 -- SQL Server checks MSDB sysjobservers table to make sure there isn't --already a job with the same name IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'test') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'testjob') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''testjob'' since there is already a multi-server job with this name.', 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'testjob' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'testjob', @owner_login_name = N'sa', @description = N'This is a test job', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- the meat of the scheduling code: -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Simple select statement', @command = N'select * from authors', @database_name = N'pubs', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule for test job', @enabled = 1, @freq_type = 8, @active_start_date = 20001031, @active_start_time = 0, @freq_interval = 20, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 2, @active_end_date = 20020101, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:


About the author

Baya Pavliashvili is a MCSE, MCSD and MCDBA and has three years experience with SQL Server.

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close