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
Comments