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
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best MS SQL Server Web Links: tips, tutorials, and much more.
- The Best SQL Web Links.
- Have another MS SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Do you have any technical questions about SQL or MS SQL Server administration or development? Post them--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature! Our SQL and SQL Server gurus are waiting to answer your toughest SQL Server questions.