Attribute VB_Name = "Module1"
' ***************************************************************************
' Copyright (C) 1991-2003 SQLDev.Net
'
' $BeginHeader$
'
' @file@:              CreateJob.cpp
' @author@:            Gert E.R. Drapers (GertD@SQLDev.Net)
' @description@:       Example: Creating a job using SQL-DMO
' @remarks@:
' @created@:           2003-03-02
' @lastsaved@:         2003-03-02
'
' update history:
'  @version@   @initials@      @updatedate@    @release@       @description@
'  00000       GED             2003-03-02      v1.0.0.0        created
'
' @EndHeader@
' ***************************************************************************

Option Explicit

Public Sub main()
    On Error GoTo errHandler
    
    Dim oSQLServer As SQLDMO.SQLServer2
    Set oSQLServer = New SQLDMO.SQLServer2
    
    oSQLServer.LoginSecure = True
    oSQLServer.Connect "(local)\dev"
    
    Dim oJobServer As SQLDMO.JobServer2
    Set oJobServer = oSQLServer.JobServer
    
    Dim oJob As SQLDMO.Job
    
    On Error Resume Next
    Set oJob = oJobServer.Jobs("MyJob")
    On Error GoTo errHandler
    
    If Not oJob Is Nothing Then
        oJob.Remove
        Set oJob = Nothing
        oJobServer.Jobs.Refresh
    End If
           
    Set oJob = New SQLDMO.Job
    
    With oJob
        .Name = "MyJob"
        .Description = "MyJob Description"
    End With
    
    oJobServer.Jobs.Add oJob
    
    Dim oJobStep As SQLDMO.JobStep
    Set oJobStep = New SQLDMO.JobStep
    
    With oJobStep
        .StepID = 1
        .Name = "JobStep 1"
        .SubSystem = "TSQL"
        .Command = "select db_name()"
        .DatabaseName = "tempdb"
        .DatabaseUserName = ""
        .OutputFileName = "C:\JobStep1.txt"
        .Server = oSQLServer.TrueName
        .OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
        .OnFailStep = SQLDMOJobStepAction_QuitWithFailure
        .RetryAttempts = 0
        .RetryInterval = 0
    End With
    
    oJob.JobSteps.Add oJobStep
    
    Dim oJobSchedule As SQLDMO.JobSchedule
    Dim oSchedule As SQLDMO.Schedule
    
    Set oJobSchedule = New SQLDMO.JobSchedule
    With oJobSchedule
        .Name = "JobSchedule 1"
        .Enabled = True
    End With
    
    Set oSchedule = oJobSchedule.Schedule
    With oSchedule
        .FrequencyType = SQLDMOFreq_Daily
        .FrequencyInterval = 1
        .FrequencySubDay = SQLDMOFreqSub_Hour
        .FrequencySubDayInterval = 1
        .FrequencyRelativeInterval = SQLDMOFreqRel_Unknown
        .FrequencyRecurrenceFactor = 0
        .ActiveStartDate = 20030301
        .ActiveEndDate = 99991231
        .ActiveStartTimeOfDay = 0
        .ActiveEndTimeOfDay = 235959
    End With
    
    oJob.JobSchedules.Add oJobSchedule
    
    oJob.Invoke
    
    Set oSchedule = Nothing
    Set oJobSchedule = Nothing
    Set oJobStep = Nothing
    Set oJob = Nothing
    Set oJobServer = Nothing
    
    oSQLServer.DisConnect
    Set oSQLServer = Nothing
    
    Exit Sub
    
errHandler:
    Debug.Print Err.Number & " " & Err.Description
    Resume Next
End Sub