Creating SQL Server Agent Jobs (SQL-DMO)

These examples illustrate creating SQL Server Agent jobs.

Use SQL-DMO to create a SQL Server Agent job by:

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


Note 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.


Examples
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:

' 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:

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:

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

See Also
Command Property JobStep Object
Job Object SubSystem Property

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.