newjobs.vb

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

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
'Imports Microsoft.SqlServer.Management.Common
Public Class NewJobs

    Public nserver As Server
    Public database As String
    'set up all your variables

    Dim objJobServer As JobServer
    Dim objJob As Job
    Dim objJobStep As JobStep
    Dim objJobSchedule As JobSchedule



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


        objJobServer = nserver.JobServer

        '作业名称，在同一个数据库服务器中不能重名 
        If objJobServer.Jobs.Contains("smo测试计划") Then

            objJobServer.Jobs("smo测试计划").Drop()

        End If

        '添加一个计划
        objJob = New Job(objJobServer, "smo测试计划")
        objJob.Create()
        objJob.ApplyToTargetServer(nserver.Name)

        objJobStep = New JobStep(objJob, "步骤1")
        objJobStep.DatabaseName = database
        objJobStep.OutputFileName = "d:\temp\testsmojob.log"

        objJobStep.SubSystem = AgentSubSystem.TransactSql
        objJobStep.Command = "Use master" + vbCrLf + "go" + vbCrLf + "sp_dboption " + _
                        database + ",single, true" + vbCrLf + "go" + vbCrLf _
                            + "DBCC CHECKDB ('" + database + "', REPAIR_FAST)" + vbCrLf + "go"

        objJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
        objJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
        objJobStep.Create()


        objJobSchedule = New JobSchedule(objJob, "数据库" + database + "检测计划")
        'the start date is the year followed by the month, followed by the date - this has to be represented as a Long
        Dim lStartDate As New Date(Val(tbStartDate.Text.Substring(0, 4)), Val(tbStartDate.Text.Substring(4, 2)), Val(tbStartDate.Text.Substring(6, 2)))
        objJobSchedule.ActiveStartDate = lStartDate
        'the time is represented on a 24 hour clock - hours, minutes, seconds - also as a Long
        Dim lStartTime As New TimeSpan(Val(tbStartTime.Text.Substring(0, 2)), Val(tbStartTime.Text.Substring(2, 2)), Val(tbStartTime.Text.Substring(3, 2)))

        objJobSchedule.ActiveStartTimeOfDay = lStartTime
        objJobSchedule.FrequencySubDayTypes = FrequencySubDayTypes.Once
        objJobSchedule.FrequencyInterval = Val(tbInterval.Text)
        objJobSchedule.IsEnabled = True
        objJobSchedule.Create()

        '        objJob.AddSharedSchedule(objJobSchedule.ID)


        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
End Class