The Job Schedule

Now, here comes the important part, the scheduling of the job, the job has been created but now we have to schedule the job so that it runs at a specific date and time.

JobSchedule object exposes the  attributes of a single SQL Server Agent executable job schedule.

   Dim oJobSchedule As SQLDMO.JobSchedule
   
   Set oJobSchedule = New SQLDMO.JobSchedule
   
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   'Schedule the task!
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You can calculate any time and date for your task to start execution, it solely depends on your choice or requirement. We have calculated the year, month and day separately.

   Dim startyear, startmonth, startday
   
   'Indicate execution scheduled for everyday by using
   'the FrequencyType and FrequencyInterval properties.
   oJobSchedule.Name = JobID
   oJobSchedule.Schedule.FrequencyType = SQLDMOFreq_OneTime

We want to execute the task only once so we have set the frequency type to single time.
   
   'Set the ActiveStartDate to indicating the date on
   'which the schedule becomes active. Start date is
   'today's date
   
   Dim mydate
   mydate = DateAdd("h", CInt(Num_Of_Hours), Now())
       
   Dim hr, min, sec
   hr = Hour(mydate)
   min = Minute(mydate)
   sec = Second(mydate)
   
   Dim mytime
   mytime = hr & min & sec
   
   startyear = DatePart("yyyy", mydate)
   startmonth = DatePart("m", mydate)
   startday = DatePart("d", mydate)
   
   If Len(startmonth) < 2 Then startmonth = "0" & startmonth
   If Len(startday) < 2 Then startday = "0" & startday
   
   oJobSchedule.Schedule.ActiveStartDate = startyear & startmonth & startday
   
Activestartdate property indicates the first effective date for a schedule.

   'Set the ActiveStartTimeOfDay property to indicate the
   'scheduled execution time on each day
   
   oJobSchedule.Schedule.ActiveStartTimeOfDay = mytime

Activestarttimeofday property indicates the start time of the day for a schedule.
   
   'Indicate that the schedule never expires
   oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
   oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME

Similarly, we have to provide the activeenddate and time for the job. We have set these properties to SQLDMO_NOENDDATE and SQLDMO_NOENDTIME which means that the job will never expire until is executed.
   
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   'Add task to scheduler
   'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
   
   oJob.JobSchedules.Add oJobSchedule