SQL-DMO
Controlling Job Step Logic
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.
| Step | On success... | On failure... |
|---|---|---|
| 1 | Continue to next step (2) | Branch to Step 4 |
| 2 | Continue to next step (3) | Branch to Step 4 |
| 3 | Quit reporting success | Quit reporting failure |
| 4 | Branch to Step 3 | Quit reporting failure |
' 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