Controlling Job Step Logic (SQL-DMO)

This example illustrates controlling SQL Server Agent job flow-of-control logic implemented in job step definitions.

SQL Server Agent jobs implement simple flow-of-control logic allowing jobs to branch based on success or failure of any one step. This example illustrates application of job logic by creating a job in four steps where:

• Steps 1 and 2 check the integrity of database filegroups.
• Step 3 backs up the filegroups.
• Step 4 attempts repair of the database on failure of an integrity check.

Job execution begins with step 1. Flow-of-control logic in the job directs execution as:

' DBCC CHECKFILEGROUP ('PRIMARY') WITH NO_INFOMSGS

' DBCC CHECKFILEGROUP ('NorthwindTextImg') WITH NO_INFOMSGS

' BACKUP DATABASE [Northwind]

'  FILEGROUP = N'PRIMARY',  FILEGROUP = N'NorthwindTextImg'

' TO [NorthDev1], [NorthDev2]

' WITH  NOINIT ,  NOUNLOAD ,

'  NAME = N'Northwind_FileGroups_9/21/98_2:30:26 PM',

'  NOSKIP ,  STATS = 10,

'  Description = N'Backup of PRIMARY and NorthwindTextImg filegroups.',

'  NOFORMAT

' DBCC CHECKDB ('Northwind', REPAIR_FAST ) WITH NO_INFOMSGS

Dim oJob As New SQLDMO.Job

Dim oJobStep As SQLDMO.JobStep

' Create the SQL Server Agent job.

oJob.Name = "Backup_Northwind_Filegroups"

oSQLServer.JobServer.Jobs.Add oJob

' Alter the job, adding job steps and setting starting step.

oJob.BeginAlter

' First step. DBCC CHECKFILEGROUP ('PRIMARY') in database Northwind.

Set oJobStep = New SQLDMO.JobStep

oJobStep.Name = "CHECKFILEGROUP_PRIMARY"

oJobStep.StepID = 1

oJobStep.SubSystem = "TSQL"

oJobStep.DatabaseName = "Northwind"

oJobStep.Command = _

    "DBCC CHECKFILEGROUP ('PRIMARY') WITH NO_INFOMSGS"

' Set job logic. On success of Step 1, continue at next step.

oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep

' On failure of Step 1, branch to Step 4 which will attempt

' database repair. Note: the step number must be assigned prior

' to setting the action property.

oJobStep.OnFailStep = 4

oJobStep.OnFailAction = SQLDMOJobStepAction_GotoStep

oJob.JobSteps.Add oJobStep

' Second step. DBCC CHECKFILEGROUP ('NorthwindTextImg') in database

' Northwind.

Set oJobStep = New SQLDMO.JobStep

oJobStep.Name = "CHECKFILEGROUP_NorthwindTextImg"

oJobStep.StepID = 2

oJobStep.SubSystem = "TSQL"

oJobStep.DatabaseName = "Northwind"

oJobStep.Command = _

    "DBCC CHECKFILEGROUP ('NorthwindTextImg') WITH NO_INFOMSGS"

' Set job logic. On success of Step 2, continue at next step, backing

' up the database.

oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoNextStep

' On failure of Step 2, branch to Step 4 which will attempt

' database repair. Note: the step number must be assigned prior

' to setting the action property.

oJobStep.OnFailStep = 4

oJobStep.OnFailAction = SQLDMOJobStepAction_GotoStep

oJob.JobSteps.Add oJobStep

' Third step. On success of both Step 1 and 2, or on successful

' database repair implemented in Step 4, backup the filegroups

' PRIMARY and NorthwindTextImg from the database Northwind.

Set oJobStep = New SQLDMO.JobStep

oJobStep.Name = "Backup Northwind filegroups"

oJobStep.StepID = 3

oJobStep.SubSystem = "TSQL"

oJobStep.Command = _

    "BACKUP DATABASE [Northwind]  " & _

    " FILEGROUP = N'PRIMARY', FILEGROUP = N'NorthwindTextImg'  " & _

    "TO [NorthDev1], [NorthDev2]" & _

    "WITH  NOINIT ,  NOUNLOAD , " & _

    " NAME = N'Northwind_FileGroups_9/21/98_2:30:26 PM', " & _

    " NOSKIP ,  STATS = 10," & _

    " Description = " & _

        "N'Backup of PRIMARY and NorthwindTextImg filegroups.', " & _

    " NOFORMAT"

' Set job logic. On success or failure, quit reporting execution

' completion status.

oJobStep.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess

oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure

oJob.JobSteps.Add oJobStep

' Fourth step. DBCC CHECKDB ('Northwind', REPAIR_FAST ). Executed only

' on failure of either steps 1 or 2.

Set oJobStep = New SQLDMO.JobStep

oJobStep.Name = "CHECKDB_Northwind_With_Repair"

oJobStep.StepID = 4

oJobStep.SubSystem = "TSQL"

oJobStep.Command = _

    "DBCC CHECKDB ('Northwind', REPAIR_FAST ) WITH NO_INFOMSGS"

' Set job logic. On success, branch to Step 3, backing up the database.

' Note: the step number must be assigned prior to setting the action

' property.

oJobStep.OnSuccessStep = 3

oJobStep.OnSuccessAction = SQLDMOJobStepAction_GotoStep

' On failure, quit job reporting failure.

oJobStep.OnFailAction = SQLDMOJobStepAction_QuitWithFailure

oJob.JobSteps.Add oJobStep

' Set the starting step for the job.

oJob.StartStepID = 1

' Alter the job.

oJob.DoAlter

See Also