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 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