|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Announcements
Want a new Job?
Chapters
Services
Feature Zones
|
Note: This is an unedited contribution. If this article is inappropriate,
needs attention or copies someone else's work without reference then please
Report This Article
IntroductionThere 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:
Using the codeThis application has three components:
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:
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:
Points of InterestHistory
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||