Introduction
There are many ways to schedule a task on a web server. For example, you can use Windows Task Scheduler and SQL Server Agent. This application is unique in that you can:
- schedule jobs using a web browser
- extend the scheduling application to include your own features
Using the Code
This application has three components:
- A Windows Service that will run a job (DOS command text)
- A Web Application that will let you schedule a job
- MS SQL Server database Stored Procedure to get the list of jobs that need to run
Job and schedule information are stored in a MS SQL Server database. The web application lets you create a job with a private or a shared schedule. A shared schedule makes scheduling jobs more manageable. The scheduler supports seven schedule types:
Type | Example |
Hourly | Run the schedule every hour |
Daily | Run job after a number of days |
Weekly | Run job on the following weekdays (Monday, Tuesday, etc.) |
Weekly skip | Run job on the following weekdays (Monday or Tuesday) skipping every other week |
Week number | Run job on the following weekdays (Monday or Tuesday); second week of month on the following months (January, February) |
Calendar | Run job on the following days (1st, 2nd, and the last day of the month); on the following months (January, February) |
Run once | |
Each schedule type supports a time window with a start and end time.
Below is the Entity Relationship Diagram for the database:
The Windows Service has a timer that fires every minute. The event calls the GetJobsToRun
Stored Procedure that lists the jobs that need to run. As each job runs, LastStatus
and LastRunTime
are updated. Also, the JobHistory
table keeps the history of every time a job runs.
Below is the code for the GetJobsToRun
Stored Procedure:
CREATE PROCEDURE GetJobsToRun
AS
SELECT JobId, JobType, JobName, CommandText
FROM Job
WHERE JobId in (
SELECT j.JobId
FROM Schedule s
INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
WHERE s.ScheduleType = 'Hourly'
and GetDate() between DATEADD(Hour, s.StartHour,
DATEADD(minute, s.StartMin, s.StartDate))
and Coalesce(s.EndDate,'12/12/2078')
and (j.LastRunTime is null
or DATEDIFF(minute, j.LastRunTime, GetDate()) >=
(EveryHour*60) + EveryMinute)
UNION
SELECT j.JobId
FROM Schedule s
INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
WHERE s.ScheduleType = 'Daily'
and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078')
and (j.LastRunTime is null or
DATEDIFF(Day, j.LastRunTime, GetDate()) > s.RepeatDays)
and (s.StartHour*60) + s.StartMin =
(DatePart(hour,GetDate())*60) + DatePart(minute,GetDate())
UNION
SELECT j.JobId
FROM Schedule s
INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
INNER JOIN ScheduleWeek w ON w.ScheduleId = j.ScheduleId
WHERE s.ScheduleType = 'Weekly'
and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078')
and DATEPART (weekday , GetDate()) = w.WeekDayId
and ((s.StartHour*60) + s.StartMin) <=
(DatePart(hour,GetDate())*60) + DatePart(minute,GetDate())
and (j.LastRunTime is null
or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0)
UNION
SELECT j.JobId
FROM Schedule s
INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
INNER JOIN ScheduleWeek w ON w.ScheduleId = j.ScheduleId
WHERE s.ScheduleType = 'WeeklySkip'
and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078')
and DATEPART (weekday , GetDate()) = w.WeekDayId
and DATEDIFF(week, j.LastRunTime, GetDate()) >= s.RepeatWeeks
and ((s.StartHour*60) + s.StartMin) <=
(DatePart(hour,GetDate())*60) + DatePart(minute,GetDate())
and (j.LastRunTime is null
or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0)
UNION
SELECT j.JobId
FROM Schedule s
INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
INNER JOIN ScheduleWeek w ON w.ScheduleId = j.ScheduleId
INNER JOIN ScheduleMonth m ON m.ScheduleId = j.ScheduleId
WHERE s.ScheduleType = 'WeekNumber'
and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078')
and w.WeekDayId = DATEPART (weekday , GetDate())
and m.MonthId = DATEPART (month , GetDate())
and s.WeekOfMonth =
(datepart(ww,GetDate())) + 1 -
datepart(ww,dateadd(dd,-(datepart(dd,GetDate())-1),GetDate()))
and ((s.StartHour*60) + s.StartMin) <=
(DatePart(hour,GetDate())*60) + DatePart(minute,GetDate())
and (j.LastRunTime is null
or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0)
UNION
SELECT j.JobId
FROM Schedule s
INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
INNER JOIN ScheduleMonth m ON m.ScheduleId = j.ScheduleId
INNER JOIN ScheduleDay d ON d.ScheduleId = j.ScheduleId
WHERE s.ScheduleType = 'Calendar'
and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078')
and DATEPART (month , GetDate()) = m.MonthId
and (DATEPART (day , GetDate()) = d.DayId
or (d.DayId = 32 and
DAY(DATEADD(d, -DAY(DATEADD(m,1,GetDate())),DATEADD(m,1,GetDate())))
= DATEPART(day ,GetDate()))
)
and ((s.StartHour*60) + s.StartMin) <=
(DatePart(hour,GetDate())*60) + DatePart(minute,GetDate())
and (j.LastRunTime is null or DATEDIFF(Day, j.LastRunTime, GetDate()) > 0)
UNION
SELECT j.JobId
FROM Schedule s
INNER JOIN Job j ON s.ScheduleId = j.ScheduleId
WHERE s.ScheduleType = 'Once'
and GetDate() between s.StartDate and Coalesce(s.EndDate,'12/12/2078')
and ((s.StartHour*60) + s.StartMin) <=
(DatePart(hour,GetDate())*60) + DatePart(minute,GetDate())
and (j.LastRunTime is null)
)
Below is the code for the RunDosCommand
function:
Function RunDosCommand(ByVal sCommandText As String, _
Optional ByVal iTimeOutSec As Integer = 1) As String
Dim iPos As Integer = sCommandText.IndexOf(" ")
Dim sFileName As String
Dim sArguments As String = ""
If iPos = -1 Then
sFileName = sCommandText
Else
sFileName = sCommandText.Substring(0, iPos)
sArguments = sCommandText.Substring(iPos + 1)
End If
Dim sRet As String
Dim oProcess As Process = New Process
oProcess.StartInfo.UseShellExecute = False
oProcess.StartInfo.RedirectStandardOutput = True
oProcess.StartInfo.FileName = sFileName
oProcess.StartInfo.Arguments = sArguments
oProcess.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
oProcess.StartInfo.CreateNoWindow = True
oProcess.Start()
oProcess.WaitForExit(1000 * iTimeOutSec)
If Not oProcess.HasExited Then
oProcess.Kill()
Return "Timeout"
End If
sRet = oProcess.StandardOutput.ReadToEnd()
If oProcess.ExitCode <> 0 And sRet = "" Then
sRet = "ExitCode: " & oProcess.ExitCode
End If
oProcess.Close()
Return sRet
End Function
A free third-party calendar component is used from dynarch.com.
Deployment
Here are the steps to deploy this application:
- Connect to SQL Server and create a database called
JobScheduler
. - Add a user to the database that would have write access.
- Open the SQL folder. Run ScheduleSchema.sql against your database.
- Run GetJobsToRun.sql against your database.
- Copy the WebScheduler folder to C:\Inetpub\wwwroot\ and make it a virtual directory.
- Double click on the C:\Inetpub\wwwroot\WebScheduler\connect.udl file and point it to the
JobScheduler
database. Test the connection and click OK. - Copy the WinJobService folder to your programs folder.
- Double click on the <Programs Folder>\WinJobService\connect.udl file and point it to the
JobScheduler
database. Test the connection and click OK. (You can also copy the file you made in step #6 to this location.) - Open the
WinJobService
(WinJobService\WinJobService.sln) project in Microsoft Visual Studio .NET 2003 and compile it. - Register the server by running the Service registration script: WinJobService\bin\Install.vbs. You can uninstall the service later by running Uninstall.vbs.