Web Based Job Scheduler

• Download JobScheduler.zip - 442.0 KB

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 lists jobs that need to run.

Job and Schedule information are stored in 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 seek 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 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 GetJobsToRun stored procedure that lists jobs that need to run. As each job runs job LastStatus and LastRunTime are updated. Also JobHistory table keeps the history of every time a job runs.

Below is the code for GetJobsToRun stored proc:

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) -- run once

)

Below is the code for 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.

Steps to deploy this application:

1. Connect to your SQL server and create a database called JobScheduler
2. Add a user to the database that would have writer access
3. Open SQL folder. Run ScheduleSchema.sql against your database.
4. Run GetJobsToRun.sql against your database.
5. Copy WebScheduler folder to C:\Inetpub\wwwroot\ and make it a virtual directory.
6. Double click on C:\Inetpub\wwwroot\WebScheduler\connect.udl file and point it to the JobScheduler database. Test the connection and click ok.
7. Copy WinJobService folder to your programs folder.
8. Double click on <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.)
9. Open WinJobService (WinJobService\WinJobService.sln) project in Microsoft Visual Studio .NET 2003 and compile it.
10. Register the server by running Service registration script: WinJobService\bin\Install.vbs. You can later uninstall the service by running Uninstall.vbs.

Points of Interest

History