Using DMO to Execute a Job

March 22, 2001

In previous articles about DMO I've talked about using DMO for jobs. In this article we'll look at how to use DMO to run a job - which might even consist of more DMO!

Why execute a job rather than just execute the task directly? The same reason you should use stored procedures instead of code - it's faster, and more importantly, it gives you a layer of abstraction. By saving the job on the server, the DBA can easily alter it without having to have an application recompiled (or even a registry setting changed).

Here is some code that shows how to connect to the server and run a job:

Sub RunAJob(ServerName As String, JobName As String)
Dim oServer As SQLDMO.SQLServer
Dim oJob As SQLDMO.Job
On Error GoTo Handler
'simple err checking
If ServerName = "" Or JobName = "" Then
MsgBox "You MUST provide the server name and the name of the job you want to execute.", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server using a trusted connection
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
'run the job
Set oJob = oServer.JobServer.Jobs(JobName)
If oJob.CurrentRunStatus = SQLDMOJobExecution_Idle Then
'show a little info just to look at the job properties
MsgBox "Job was last run at " & oJob.LastRunDate & " " & oJob.LastRunTime & "."
'Im using invoke here, but if you don't want to start at the default step you
'should use the Start method
MsgBox "Job cannot be executed"
End If
Set oJob = Nothing
'standard clean up
On Error Resume Next
Set oServer = Nothing
Exit Sub
MsgBox Err.Number & "-" & Err.Description, vbCritical + vbOKOnly, "Error"
GoTo Cleanup
End Sub