Scheduling SQL Server Agent Jobs
SQL-DMO will be removed in the next version of Microsoft SQL Server. Avoid using SQL-DMO feature in new development work, and plan to modify applications that use SQL-DMO.
These examples illustrate scheduling execution for SQL Server Agent jobs by creating and populating SQL Distributed Management Objects (SQL-DMO) JobSchedule objects.
A job can be run by SQL Server Agent when it contains at least one step and an execution target. Use the Start method of the Job object to direct unscheduled execution of an executable job. Create schedules for jobs when automated execution of the job is desired.
Examples
A. Scheduling a Job for Single Execution
This example illustrates creating a job schedule defining a single execution time for a SQL Server Agent job.
Dim oJobSchedule As New SQLDMO.JobSchedule
Dim oJob As SQLDMO.Job
' Get the job to target. Note: Create and connect of SQLServer object
' is not illustrated in this example.
Set oJob = oSQLServer.JobServer.Jobs("Backup_Northwind_Filegroups")
' Set the schedule name.
oJobSchedule.Name = "Single_Execution"
' Indicate a single scheduled execution by using the
' FrequencyType property.
oJobSchedule.Schedule.FrequencyType = SQLDMOFreq_OneTime
' Use the ActiveStartDate and ActiveStartTimeOfDay properties
' to indicate the scheduled execution time for a JobSchedule
' object implementing a single run.
oJobSchedule.Schedule.ActiveStartDate = "19980922"
oJobSchedule.Schedule.ActiveStartTimeOfDay = "130000"
' Optional, but cleaner. Indicated that schedule never expires.
oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
' Alter the job, adding the new schedule.
oJob.BeginAlter
oJob.JobSchedules.Add oJobSchedule
oJob.DoAlter
B. Scheduling a Job for Execution Once Per Day
This example illustrates creating a job schedule defining daily execution for a SQL Server Agent job.
Dim oJobSchedule As New SQLDMO.JobSchedule
Dim oJob As SQLDMO.Job
Dim StartYear, StartMonth, StartDay As String
' Get the job to target. Note: Create and connect of SQLServer object
' is not illustrated in this example.
Set oJob = oSQLServer.JobServer.Jobs("Backup_Northwind_Filegroups")
' Set the schedule name.
oJobSchedule.Name = "OncePerDay_Execution"
' Indicate execution scheduled for every day by using the
' FrequencyType and FrequencyInterval properties.
oJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Daily
oJobSchedule.Schedule.FrequencyInterval = 1
' Set the ActiveStartDate to indicating the date on which the
' schedule becomes active. Start date is today's date.
StartYear = DatePart("yyyy", Date)
StartMonth = DatePart("m", Date)
StartDay = DatePart("d", Date)
If Len(StartMonth) < 2 Then StartMonth = "0" & StartMonth
If Len(StartDay) < 2 Then StartDay = "0" & StartDay
oJobSchedule.Schedule.ActiveStartDate = _
StartYear & StartMonth & StartDay
' Set the ActiveStartTimeOfDay property to indicate the scheduled
' execution time on each day (2:32 AM).
oJobSchedule.Schedule.ActiveStartTimeOfDay = "23200"
' Indicated that the schedule never expires.
oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
' Alter the job, adding the new schedule.
oJob.BeginAlter
oJob.JobSchedules.Add oJobSchedule
oJob.DoAlter
C. Scheduling a Job for Execution Multiple Times Per Day
This example illustrates creating a job schedule that defines hourly execution for a SQL Server Agent job.
Dim oJobSchedule As New SQLDMO.JobSchedule
Dim oJob As SQLDMO.Job
Dim StartYear, StartMonth, StartDay As String
' Get the job to target. Note: Create and connect of SQLServer object
' is not illustrated in this example.
Set oJob = oSQLServer.JobServer.Jobs("NetSend")
' Set the schedule name.
oJobSchedule.Name = "Hourly_Execution"
' Indicate execution scheduled for every day by using the
' FrequencyType and FrequencyInterval properties.
oJobSchedule.Schedule.FrequencyType = SQLDMOFreq_Daily
oJobSchedule.Schedule.FrequencyInterval = 1
' Indicate hourly execution by using the FrequencySubDay
' and FrequencySubDayInterval properties.
oJobSchedule.Schedule.FrequencySubDay = SQLDMOFreqSub_Hour
oJobSchedule.Schedule.FrequencySubDayInterval = 1
' Set the ActiveStartDate to indicating the date on which the
' schedule becomes active. Start date is today's date.
StartYear = DatePart("yyyy", Date)
StartMonth = DatePart("m", Date)
StartDay = DatePart("d", Date)
If Len(StartMonth) < 2 Then StartMonth = "0" & StartMonth
If Len(StartDay) < 2 Then StartDay = "0" & StartDay
oJobSchedule.Schedule.ActiveStartDate = _
StartYear & StartMonth & StartDay
' Set the ActiveStartTimeOfDay property to indicate the time at
' which the schedule becomes active (12:00 AM).
oJobSchedule.Schedule.ActiveStartTimeOfDay = "00000"
' Indicated that the schedule never expires.
oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
' Alter the job, adding the new schedule.
oJob.BeginAlter
oJob.JobSchedules.Add oJobSchedule
oJob.DoAlter
D. Scheduling a Job for Execution Once Per Relative Interval
This example illustrates creating a job schedule defining once a month execution for a SQL Server Agent job. The job schedule directs execution to a day relative to the start day of the month.
Dim oJobSchedule As New SQLDMO.JobSchedule
Dim oJob As SQLDMO.Job
Dim StartYear, StartMonth, StartDay As String
' Get the job to target. Note: Create and connect of SQLServer object
' is not illustrated in this example.
Set oJob = oSQLServer.JobServer.Jobs("Backup_Northwind_Filegroups")
' Set the schedule name.
oJobSchedule.Name = "Second_Friday"
' For monthly, relative day scheduling, the FrequencyType,
' FrequencyInterval, FrequencyRecurrenceInterval, and
' FrequencyRelativeInterval properties together define the
' schedule.
'
' FrequencyType and FrequencyRecurrence factor indicate relative
' and every month execution.
oJobSchedule.Schedule.FrequencyType = SQLDMOFreq_MonthlyRelative
oJobSchedule.Schedule.FrequencyRecurrenceFactor = 1
' FrequencyInterval indicates the day where 0 = Sunday, 7 =
' Saturday, and other values indicate "weekday" or "weekend
' day".
oJobSchedule.Schedule.FrequencyInterval = 6
' FrequencyRelativeInterval indicates the day relative to
' the start of the month.
oJobSchedule.Schedule.FrequencyRelativeInterval = _
SQLDMOFreqRel_Second
' Set the ActiveStartDate property to indicating the date on which the
' schedule becomes active. Start date is today's date.
StartYear = DatePart("yyyy", Date)
StartMonth = DatePart("m", Date)
StartDay = DatePart("d", Date)
If Len(StartMonth) < 2 Then StartMonth = "0" & StartMonth
If Len(StartDay) < 2 Then StartDay = "0" & StartDay
oJobSchedule.Schedule.ActiveStartDate = _
StartYear & StartMonth & StartDay
' Set the ActiveStartTimeOfDay property to indicate the scheduled
' job execution time (9:53:22 PM).
oJobSchedule.Schedule.ActiveStartTimeOfDay = "215322"
' Indicated that the schedule never expires.
oJobSchedule.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
oJobSchedule.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
' Alter the job, adding the new schedule.
oJob.BeginAlter
oJob.JobSchedules.Add oJobSchedule
oJob.DoAlter