Click here to Skip to main content
Click here to Skip to main content

A scheduler GUI for SQL Server Agent

, 10 May 2012
Rate this:
Please Sign up or sign in to vote.
An alternative to SQL Server Management Studio for scheduling SQL Server Agent jobs

Introduction

Imagine you have some predefined SQL Server jobs and you want to give your users the ability to schedule them as they wish. You want to leverage the powerful scheduler features of SQL Server Agent (one-time/recurring schedules, daily/weekly/monthly recurring frequency, multiple schedules combined for a single job, etc.), but you don't want your users to access the SQL Server Management Studio, you don't want to give them too high permissions levels, you don't want to give them access to the jobs but just to the schedules and you want to offer these scheduling facilities directly inside your desktop or web application.

Don't you think it would be great to have in your application something similar to the well-known "Job Schedule Properties" dialog box of SQL Server Management Studio (SSMS)?

Job Schedule Properties

Well, the piece of code presented in this article does exactly this: it provides a way to include in your application a graphical user interface (GUI) for creating and editing custom schedules for existing SQL Server Agent jobs. This can be useful in any situation you need to add to your application some user-friendly scheduling facilities to control your T-SQL jobs.

The basic idea

The basic idea is fairly simple: we want to provide an alternative GUI for the creation, editing and deletion of schedules connected with existing, predefined SQL Server Agent jobs.

As any SQL Server developer knows, any configuration operated via the SQL Server Management Studio (SSMS) is scriptable, being the SSMS itself just a client that interacts with the server-side services. In our specific scenario, SQL Server Agent jobs' schedules are stored in the msdb system database, and they are managed through well documented system procedures. So, the task of implementing a GUI for interactiong with jobs' schedules is just a matter of understanding their model and create an interface that makes it easy for the user to call the proper stored procedures.

The very minimum set of procedures to be considered is as follows:

  • sp_help_jobschedule, useful to retrieve the schedules associated to a given job;
  • sp_add_schedule, useful to create a new schedule;
  • sp_attach_schedule, useful to attach a newly created schedule to a given job;
  • sp_update_schedule, useful to modify an existing schedule;
  • sp_detach_schedule, useful to remove a schedule from a job. To be more precise: it detaches a schedule from a given job and then delete the detached schedule if it is no more referenced by any other job. In fact, in general, a single SQL Agent schedule could be used by more than one job. For the scope of this article, the jobs we are considering are associated to schedules not used by other jobs.

All the mentioned stored procedures are documented on MSDN at the following links:
http://msdn.microsoft.co m/en-us/library/ms176046(v=sql.100).aspx
http://msdn.microsoft.co m/en-us/library/ms187320(v=sql.100).aspx
http://msdn.microsoft.co m/en-us/library/ms186766(v=sql.100).aspx
http://msdn.microsoft.co m/en-us/library/ms187354(v=sql.100).aspx
http://msdn.microsoft.co m/en-us/library/ms186766(v=sql.100).aspx

In addition, we'll use the sysjobs_view system view to retrieve the initial list of the jobs we want to operate on.

The solution

The downloadable code package (written in C#) contains a Visual Studio 2008 solution (easily convertible to VS2010) made up of these projects:

  • SQLjobScheduler: a library DLL assembly, exposing the basic features and APIs to call the system stored procedures described above;
  • SQLjobSchedulerGUI: a Windows Forms application that implements a demo scheduler GUI for a desktop application;
  • SQLjobSchedulerWebGUI: a Silverlight component that implements a demo scheduler GUI for a web application;
  • SQLjobSchedulerWebGUI.Web: an ASP.NET project just containing a page for hosting the Silverlight component named above and an ASMX web service to provide data services to it.
If you need to add SQL Agent scheduler facilities to a Windows Forms application, you will be able to easily integrate the forms of the SQLjobSchedulerGUI project. If you need those facilities in a web application, you will be able to integrate the Silverlight component included in the SQLjobSchedulerWebGUI project. If you want to create your own user interface, you will be able to reuse at least the APIs exposed by the SQLjobScheduler DLL assembly.

SQLjobScheduler APIs

Here is a quick list of the public functions exposed by the SQLjobScheduler DLL library assembly:

LoadSQLjobs: Retrieves the jobs list that the GUI will show to the user in order to let him choose the job on which he wants to create, edit or delete schedules. Because in a general scenario you don't want the user to operate on all the jobs defined in msdb, you can provide the list of your "target categories": this way, the retrieved jobs list will include only jobs pertaining to the specified categories.

In order to associate a job to a specific category, operate on the "Category" property on the "Job Properties" dialog box in SSMS:

Job Properties

In order to manage your own custom job categories, you can access the "Manage Job Categories" window in SSMS by activating the command "Manage Job Categories" on the SQL Server Agent Jobs' contextual menu:

Job Properties

LoadSQLschedules: Retrieves the schedules pertaining to a given job. This method is typically called when the user has selected a job to operate on, and the application needs to show him all the schedules associated with the selected job.

CreateSQLschedule: Create a schedule on msdb using the given information. The method basically does a call to the sp_add_schedule system stored procedure (in order to create the schedule) and then calls the sp_attach_schedule system stored procedure (in order to attach the newly created schedule to the given job).

UpdateSQLschedule: Modifies an existing schedule on msdb using the given information. The method calls the sp_update_schedule system stored procedure.

DeleteSQLschedule: Deletes an existing schedule from msdb, given its identifier. The method calls the sp_detach_schedule system stored procedure to detach the schedule from the given job and then delete the detached schedule if it is no more referenced by any other job (keep in mind that, in general, a single SQL Agent schedule could be used by more than one job).

All the methods described above receive as a parameter the connection string to the msdb database, that your application has to provide, after having properly retrieved it from some configuration (as it happens in the demo applications: see app.config for SQLjobSchedulerGUI and web.config for SQLjobSchedulerWebGUI.Web). Of course the user specified in the connection string must have sufficient permission to operate on SQL Server Agent schedules.

The methods above also use, as input parameters or return values, instances of the SQLjob and the SQLschedule class, both defined in SQLjobScheduler library as follows, to mimic the data structure of jobs and schedules entities in msdb:

  public class SQLjob
  {
    public string job_id { get; set; }
    public string originating_server { get; set; }
    public string name { get; set; }
    public int enabled { get; set; }
    public string description { get; set; }
    public int category_id { get; set; }
    public DateTime date_created { get; set; }
    public DateTime date_modified { get; set; }
    public int version_number { get; set; }
  }
  public class SQLschedule
  {
    public int schedule_id { get; set; }
    public string schedule_uid { get; set; }
    public int originating_server_id { get; set; }
    public string name { get; set; }
    public string owner_sid { get; set; }
    public int enabled { get; set; }
    public int freq_type { get; set; }
    public int freq_interval { get; set; }
    public int freq_subday_type { get; set; }
    public int freq_subday_interval { get; set; }
    public int freq_relative_interval { get; set; }
    public int freq_recurrence_factor { get; set; }
    public DateTime active_start_date { get; set; }
    public DateTime active_end_date { get; set; }
    public DateTime active_start_time { get; set; }
    public DateTime active_end_time { get; set; }
    public DateTime date_created { get; set; }
    public DateTime date_modified { get; set; }
    public int version_number { get; set; }
  }

Demo GUI for Windows Forms applications

The demo GUI for Windows Forms applications is included in the SQLjobSchedulerGUI project. It consists of a first form where the user is asked to select the job he wants to work on, and of a second form that has been designed to resemble the SSMS "Job Schedule" dialog box and to offer the same capabilities, as shown in the image below (notice the daily/weekly/monthly frequency options, for example):

Edit Schedule for WinForms 
applications

The forms' code directly uses the SQLjobScheduler APIs described above.

Demo GUI for web applications

The demo GUI for web applications has been implemented in the SQLjobSchedulerWebGUI project as a Silverlight component (it uses Silverlight version 3 for VS2008, but it can be easily converted to more recent versions). Again, it is made of a first page useful for selecting a job to work on and of a second page that has been designed to resemble the SSMS "Job Schedule" dialog box and to offer the same capabilities (notice the daily/weekly/monthly frequency options, for example):

Edit Schedule for web applications

The Silverlight component interacts with the SQLjobSchedulerWS.ASMX web service, which acts as a wrapper on the SQLjobScheduler APIs described above.

Conclusions

This solution is not a particularly smart piece of code, but I'm sure it will be handy for anyone who needs to integrate SQL Server Agent scheduler facilities in his own Windows Forms or web applications.
I wish to thank my colleague Marco Lauriola, who indirectly and unintentionally inspired me towards this solution.

History

01/05/2012 - First version (including WinForms GUI and web Silverlight GUI).
02/05/2012 - Minor corrections in the article text.
10/05/2012 - Fixed a minor bug in schedule saving. Some more notes added in the code solution.

License

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

About the Author

Alberto Venditti
Technical Lead
Italy Italy
I was born in 1970.
 
My first computer experience dates back to early 80s, with a Sinclair ZX81.
From that time on, as many "friends" say, my IT-illness has increased year by year.
 
I graduated in Electronic Engineering and earned the following Microsoft certifications:
MCP, MCT, MCDBA, MCSD, MCAD, MCSD for .NET (early achiever).
 
I worked in IT as a developer, a teacher, a consultant, a technical writer, a technical leader.
IT knowledge applied to real life is my primary interest and focus.

Comments and Discussions

 
Questionhow to make the schedule enable tick permanent PinmemberPratikShah23663-Feb-14 18:57 
AnswerRe: how to make the schedule enable tick permanent PinmemberAlberto Venditti6-Feb-14 3:56 
Questionqns Pinmembersonysg6-Dec-13 18:45 
AnswerRe: qns PinmemberAlberto Venditti6-Feb-14 4:00 
GeneralMy vote of 5 PinmemberDomManning12-Jun-13 6:31 
GeneralMy vote of 5 PinmemberKuthuparakkal9-Aug-12 1:41 
GeneralCustom API vs Microsoft Namespace PinmemberKuthuparakkal9-Aug-12 1:41 
GeneralRe: Custom API vs Microsoft Namespace PinmemberAlberto Venditti10-Aug-12 5:22 
GeneralMy vote of 5 Pinmemberknow-life-death20-May-12 2:08 
QuestionLicense PingroupGagan111810-May-12 11:13 
AnswerRe: License PinmemberAlberto Venditti13-May-12 22:36 
NewsBug fixed PinmemberAlberto Venditti10-May-12 2:15 
QuestionBrilliant and useful post Pinmemberflyboy659-May-12 20:53 
AnswerRe: Brilliant and useful post [modified] PinmemberAlberto Venditti9-May-12 22:59 
Thank you, James.
 
The idea of adding the sp_start_job is good, even if I think it's easy enough to call that stored procedure from anywhere in a custom application code... Probably the "added value" of the scheduler GUI I'm presenting here is just in the fact that the programmer doesn't have to dive deep in the complicated parameters of sp_add_schedule, having a GUI ready to be used. Compared to sp_add_schedule, calling sp_start_job seems like drinking a glass of water, doesn't it?
 
About the algorithmsuite, I don't see it in the original ServiceReferences.ClientConfig (maybe VS added it while upgrading the solution?).
 
Cheers, AV

modified 10-May-12 5:33am.

GeneralMy vote of 4 PinmemberBassam Saoud2-May-12 4:17 
QuestionMy vote of 4 PinmemberBassam Saoud2-May-12 4:16 
AnswerRe: My vote of 4 [modified] PinmemberAlberto Venditti2-May-12 7:07 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 10 May 2012
Article Copyright 2012 by Alberto Venditti
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid