Export (0) Print
Expand All

Creating 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 creating SQL Server Agent jobs.

Use SQL Distributed Management Objects (SQL-DMO) to create a SQL Server Agent job by:

  • Creating and populating a Job object.

  • Adding the Job object to the Jobs collection of a JobServer object.

  • Creating and populating one or more JobStep objects.

  • Altering the Job object, by adding the JobStep object(s) created to the JobSteps collection.

With the job created, indicate an execution target. For more information about examples, see Targeting SQL Server Agent Jobs.

NoteNote

SQL Server Agent implements executable subsystems for job steps. The text defining the administrative task is interpreted by the selected executable subsystem. In the examples that follow, all job steps in the job created by the example use a single executable subsystem. This implementation is imposed for clarity only.

A. Creating a Job Containing a Transact-SQL Command Batch

This example illustrates creating a multistep job. Each job step is defined by using a Transact-SQL command batch.

This example:

  • Creates a Job object and adds the object to a Jobs collection to create a SQL Server Agent job.

  • Gets the Tables collection of a Database object.

  • For each Table object in the collection:

    • Creates a JobStep object.

    • Uses the Name property of the Table object to build a Transact-SQL command batch to set the Commandproperty of the JobStep object.

    • Builds default job control-of-flow logic.

    • Adds the JobStep object to the JobSteps collection of the Job object.

  • Assigns a starting step for the job and adjusts logic for the final step.

  • Commits job modifications.

    ' Table object used in iteration over Tables collection.
    Dim oTable As SQLDMO.Table
    
    Dim oJob As New SQLDMO.Job
    Dim oJobStep As SQLDMO.JobStep
    Dim idStep As Integer
    
    ' Create the SQL Server Agent job. Job will perform an update
    ' of all optimizer-supporting data distribution statistics.
    oJob.Name = "Northwind_Statistics_Update"
    oSQLServer.JobServer.Jobs.Add oJob
    
    ' Alter the job, adding job steps and setting starting step.
    oJob.BeginAlter
    
    ' Each JobStep contains the Transact-SQL command batch
    ' updating statistics for a table.
    idStep = 0
    For Each oTable In oSQLServer.Databases("Northwind").Tables
        ' Only applies to user defined tables....
        If oTable.Attributes <> SQLDMOTabAtt_SystemObject Then
            Set oJobStep = New SQLDMO.JobStep
    
            idStep = idStep + 1
    
            oJobStep.Name = "Northwind_Statistics_Update_Step_" & idStep
            oJobStep.StepID = idStep
    
            oJobStep.DatabaseName = "Northwind"
            oJobStep.SubSystem = "TSQL"
    
            ' TSQL uses the [] syntax to quote table identifers.
            oJobStep.Command = "UPDATE STATISTICS [" & oTable.Name & _
                "] WITH FULLSCAN, NORECOMPUTE"
    
            ' Default logic. Amended below.
            oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
            oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep
    
            oJob.JobSteps.Add oJobStep
        End If
    Next oTable
    
    ' Reset the logic flow for the last job step to indicate success.
    oJob.JobSteps.ItemByID(idStep).OnSuccessAction = _
        SQLDMOJobStepAction_QuitWithSuccess
    
    ' Set the starting step for the job.
    oJob.StartStepID = 1
    
    ' Alter the job.
    oJob.DoAlter
    

B. Creating a Job Containing an Operating System Command

This example illustrates creating a single-step job. The job step is defined by using an operating system command.

This example:

  • Creates a Job object and adds the object to a Jobs collection to create a SQL Server Agent job.

  • Creates a JobStep object.

  • Assigns the Command and SubSystem properties to indicate an operating system command.

  • Adds the JobStep object to the JobSteps collection of the Job object.

  • Assigns a starting step for the job and job logic.

  • Commits job modifications.

    Dim oJob As New SQLDMO.Job
    Dim oJobStep As New SQLDMO.JobStep
    
    Dim strQuote As String
    
    strQuote = Chr$(34)
    
    ' Create the SQL Server Agent job. Job will send a network
    ' popup message.
    oJob.Name = "NetSend"
    oSQLServer.JobServer.Jobs.Add oJob
    
    ' Alter the job, adding job steps and setting starting step.
    oJob.BeginAlter
    
    ' The job is implemented using a single step.
    oJobStep.Name = "NetSend_1"
    oJobStep.StepID = 1
    
    ' Set the job step exucatable subsystem. For operating
    ' system command job steps, the subsystem is "CmdExec"
    oJobStep.SubSystem = "CmdExec"
    
    ' Job step script is:
    '
    ' Net Send SEATTLE1 "Now is the time for all good men " & _
    '    "to come to the aid of the party."
    oJobStep.Command = _
        "Net Send SEATTLE1 " & strQuote & _
        "Now is the time for all good men to come to the " & _
        "aid of the party." & strQuote
    
    ' Logic for a single-step job.
    oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
    oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
    
    oJob.JobSteps.Add oJobStep
    
    ' Set the starting step for the job.
    oJob.StartStepID = 1
    
    ' Alter the job.
    oJob.DoAlter
    

C. Creating a Job Containing an Active Script Command

This example illustrates creating a single-step job. The job step is defined by using a Microsoft ActiveX script language.

This example:

  • Creates a Job object and adds the object to a Jobs collection to create a SQL Server Agent job.

  • Creates a JobStep object.

  • Assigns the Command, SubSystem, and DatabaseName properties to indicate an ActiveX language script.

  • Adds the JobStep object to the JobSteps collection of the Job object.

  • Assigns a starting step for the job and job logic.

  • Commits job modifications.

    Dim oJob As New SQLDMO.Job
    Dim oJobStep As New SQLDMO.JobStep
    
    Dim strNewLine As String
    Dim strQuote As String
    
    strNewLine = Chr$(13) & Chr$(10)
    strQuote = Chr$(34)
    
    ' Create the SQL Server Agent job. Job will perform an update
    ' of all optimizer-supporting data distribution statistics.
    oJob.Name = "Northwind_Statistics_Update_ActiveScript"
    oSQLServer.JobServer.Jobs.Add oJob
    
    ' Alter the job, adding job steps and setting starting step.
    oJob.BeginAlter
    
    ' Define the job's single step.
    oJobStep.Name = "Northwind_Statistics_Update_ActiveScript_1"
    oJobStep.StepID = 1
    
    ' Set the job step executable subsystem. For ActiveX Script
    ' job steps, the DatabaseName property records the script
    ' interpreter selected.
    oJobStep.SubSystem = "ActiveScripting"
    oJobStep.DatabaseName = "VBScript"
    
    ' Job step script is:
    '
    ' Set oSQLServer = CreateObject("SQLDMO.SQLServer")
    '
    ' oSQLServer.LoginSecure = True
    ' oSQLServer.Connect
    '
    ' oSQLServer.Databases("Northwind").UpdateIndexStatistics
    '
    ' oSQLServer.DisConnect
    ' Set oSQLServer = Nothing
    
    oJobStep.Command = _
        "Set oSQLServer = CreateObject(" & _
        strQuote & "SQLDMO.SQLServer" & strQuote & ")"
    
    oJobStep.Command = oJobStep.Command & strNewLine & strNewLine
    
    oJobStep.Command = oJobStep.Command & _
        "oSQLServer.LoginSecure = True"
    
    oJobStep.Command = oJobStep.Command & strNewLine
    
    oJobStep.Command = oJobStep.Command & _
        "oSQLServer.Connect"
    
    oJobStep.Command = oJobStep.Command & strNewLine & strNewLine
    
    oJobStep.Command = oJobStep.Command & _
        "oSQLServer.Databases(" & strQuote & "Northwind" & _
        strQuote & ").UpdateIndexStatistics"
    
    oJobStep.Command = oJobStep.Command & strNewLine & strNewLine
    
    oJobStep.Command = oJobStep.Command & _
        "oSQLServer.DisConnect"
    
    oJobStep.Command = oJobStep.Command & strNewLine
    
    oJobStep.Command = oJobStep.Command & _
        "Set oSQLServer = Nothing"
    
    oJobStep.Command = oJobStep.Command & strNewLine
    
    ' Logic for a single-step job.
    oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
    oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
    
    oJob.JobSteps.Add oJobStep
    
    ' Set the starting step for the job.
    oJob.StartStepID = 1
    
    ' Alter the job.
    oJob.DoAlter
    

Community Additions

ADD
Show:
© 2014 Microsoft