Thank this author by sharing:
By Andy Warren,
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 +
I'm seeing a intresting error on one of my SQL Instance.
Below is the script I'...
SQLServerAgent Error: Request to run job "jobname" (from User xxxx\aaaa) refused because the job alr...
Error on Connection String
Execution error while executing the procedure