Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 22, 2001

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
MsgBox "Job cannot be executed"
End If
Set oJob = Nothing
'standard clean up
On Error Resume Next
Set oServer = Nothing
Exit Sub
MsgBox Err.Number & "-" & Err.Description, vbCritical + vbOKOnly, "Error"
GoTo Cleanup
End Sub

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM