Using DMO to Execute 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
oServer.JobServer.Jobs(JobName).Invoke
Else
MsgBox "Job cannot be executed"
End If
Set oJob = Nothing
 
'standard clean up
Cleanup:
On Error Resume Next
oServer.DisConnect
Set oServer = Nothing
Exit Sub
Handler:
MsgBox Err.Number & "-" & Err.Description, vbCritical + vbOKOnly, "Error"
GoTo Cleanup
End Sub