Showing posts with label SQL Job. Show all posts
Showing posts with label SQL Job. Show all posts

Tuesday, October 5, 2010

VBScript to run an MS SQL Job

Example Usage:

Call runSQLJob("My Job")

------------------------------------------------------------


Public Sub runSQLJob(JobName)
'********************************************
' Author : Chinmay Mudholkar
' Purpose : Runs the specified SQL job.  Returns after the job execution is complete.
' Inputs : JobName: Name of the SQL job to run
' Returns : None
'********************************************

Dim jst, myCon, ObjSQL
jst = "SELECT ISNULL((SELECT CAST(J.JOB_ID AS VARCHAR(36)) FROM msdb..sysjobs j INNER JOIN msdb..sysjobactivity ja ON j.job_id = ja.job_id WHERE j.name = '" & JobName & "' AND ja.stop_execution_date IS NOT NULL AND ja.start_execution_date = (SELECT MAX(jain.start_execution_date) FROM msdb..sysjobactivity jain WHERE jain.job_id = j.job_id GROUP BY jain.job_id)), 0)"
Set myCon=Createobject("ADODB.Connection")
myCon.ConnectionString = "your connection string here"
ObjSQL = "EXEC msdb.dbo.sp_start_job @job_name = '" & JobName & "'"
myCon.Open()
myCon.Execute(ObjSQL)
Do
Wait(2) 'Wait in the beginning for the job to start and then before querying job status everytime.
Dim rs
Set rs = CreateObject("adodb.recordset")
rs.open jst, myCon, 3, 3
If Len(rs.fields(0))>1 Then
Exit Do
End If
Set rs = Nothing
Loop
myCon.Close()
End Sub

------------------------------------------------------------