Controlling Job Step Logic

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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 in the following manner.

' 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