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
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"
'open connection to server using a trusted connection
Set oServer = New SQLDMO.SQLServer
.LoginSecure = True
'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"
Set oJob = Nothing
'standard clean up
On Error Resume Next
Set oServer = Nothing
MsgBox Err.Number & "-" & Err.Description, vbCritical +