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

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