Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using DMO to Execute a Job

By Andy Warren,

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

Total article views: 5187 | Views in the last 30 days: 0
 
Related Articles
FORUM

JobName Not found error

HI All, I'm seeing a intresting error on one of my SQL Instance. Below is the script I'...

FORUM

SQLServerAgent Error: Request to run job "jobname" (from User xxxx\aaaa) refused because the job already has a pending request from User XXXX\AAAA

SQLServerAgent Error: Request to run job "jobname" (from User xxxx\aaaa) refused because the job alr...

FORUM

Error on Connection String

Error on Connection String

FORUM

SQL 2000 Connectivity error

Connectivity error

FORUM

Execution Error

Execution error while executing the procedure

Tags
programming    
sql-dmo    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones