Using DMO to Execute a Job

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

Latest Articles