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)?
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:
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:
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):
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):
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.
