Scheduling SQL Server Agent Jobs (SQL-DMO)

These examples illustrate scheduling execution for SQL Server Agent jobs by creating and populating 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

See Also