newjobs.vb

SQL Server 业已成为最为流行的几大数据库之一

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''ch06 示例４
''
''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Class NewJobs

    Public server As SQLDMO.SQLServer
    Public database As String
    'set up all your variables 
    Dim objJob As New SQLDMO.Job
    Dim objJobStep As SQLDMO.JobStep
    Dim objJobSchedule As SQLDMO.JobSchedule


    Private Sub btStartCheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btStartCheck.Click

        '作业名称，在同一个数据库服务器中不能重名 
        objJob.Name = "测试创建任务计划"

        '添加一个计划
        server.JobServer.Jobs.Add(objJob)

        'Inform SQL that you are beginning to alter the job
        objJob.BeginAlter()

        objJobStep = New SQLDMO.JobStep
        objJobStep.Name = "check database"
        'The step ID must be an integer (starting at 1) and must be unique for that job

        objJobStep.StepID = 1


        'Must be a valid database on the server
        objJobStep.DatabaseName = database
        'How the job command will be executed
        objJobStep.SubSystem = "TSQL"

        'What will be executed in 'TSQL' - here shelling a simple exe
        objJobStep.Command = "Use master" + vbCrLf + "go" + vbCrLf + "sp_dboption " + database + ",single, true" + vbCrLf + "go" + vbCrLf _
        + "DBCC CHECKDB ('" + database + "', REPAIR_FAST)" + vbCrLf + "go"


        objJobStep.OutputFileName = "d:\temp\testjob.log"

        'what the step will do on fail - possible values are:
        'QuitWithFailure
        'GotoNextStep
        'GotoStep
        'QuitWithSuccess
        'Unknown
        objJobStep.OnFailAction = SQLDMO.SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithFailure
        'what the step will do on success - as above
        objJobStep.OnSuccessAction = SQLDMO.SQLDMO_JOBSTEPACTION_TYPE.SQLDMOJobStepAction_QuitWithSuccess

        'add the step to the job
        objJob.JobSteps.Add(objJobStep)

        'on which step the job must start
        objJob.StartStepID = 1

        'commit your changes to SQL
        objJob.DoAlter()

        objJobSchedule = New SQLDMO.JobSchedule
        objJobSchedule.Name = database + "Check"
        'the start date is the year followed by the month, followed by the date - this has to be represented as a Long
        objJobSchedule.Schedule.ActiveStartDate = Val(tbStartDate.Text)
        'the time is represented on a 24 hour clock - hours, minutes, seconds - also as a Long
        objJobSchedule.Schedule.ActiveStartTimeOfDay = Val(tbStartTime.Text)
        objJobSchedule.Schedule.FrequencyType = SQLDMO.SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_Daily
        objJobSchedule.Schedule.FrequencyInterval = Val(tbInterval.Text)
        'add the schedule to the Job
        objJob.JobSchedules.Add(objJobSchedule)

        'commit the changes to SQL
        objJob.DoAlter()

        'apply the job to a specific SQL Server
        objJob.ApplyToTargetServer(server.NetName)

        'commit the changes to SQL
        objJob.DoAlter()

        'disconect from the database
        MsgBox("计划已被成功添加")


    End Sub

    Private Sub NewJobs_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
        objJob = Nothing
        objJobStep = Nothing
        objJobSchedule = Nothing
    End Sub

    Private Sub NewJobs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class