|
Stoad
Freaky Yak Linguist
1983 Posts |
 Posted - 2005-03-15 : 09:12:19
|
Add into your job you want to time-limit and stop automatically
the following Active Script (VBS) as the job's 1st step:
================================================
job_killer_script = "D:\job_killer.vbs"
this_job_name = "myJob"
time_limit_secs = "20"
os_process_to_kill = "N\A"
Set o = CreateObject("WScript.Shell")
o.Run """" & job_killer_script & """ """ & this_job_name & """ " & _
time_limit_secs & " """ & Now & """ """ & os_process_to_kill & """"
Set o = Nothing
================================================
In red the only parameters you need to change. That's all.
This is D:\job_killer.vbs script which is invoked (asynchronously) from the job's 1st,
controlling, step. It's ready to use and you need no to change anything in it. Only
check its colored lines. And, of course, this script can be invoked simultaneously by
any number of different started jobs. Hope, kill.exe (rkill.exe in win xp) is in its right
place, i.e., in %SystemRoot% folder.
================================================
Option Explicit
On Error Resume Next
Dim o, s, j, jd, jt
Dim job_name, time_limit_secs, step1_dt, os_process_to_kill
With WScript
job_name = .Arguments(0)
time_limit_secs = .Arguments(1)
step1_dt = .Arguments(2)
os_process_to_kill = .Arguments(3)
End With
WScript.Sleep 1000 * time_limit_secs
Set s = CreateObject("SQLDMO.SQLServer")
Set j = CreateObject("SQLDMO.Job")
s.LoginSecure = True
s.Connect "."
Set j = s.JobServer.Jobs(job_name)
jd = j.LastRunDate
jt = Right("00000" & j.LastRunTime, 6)
'''this is for mdy system date format:
jd = Mid(jd, 5, 2) & " " & Right(jd, 2) & " " & Left(jd, 4)
'''this is for dmy system date format:
'''jd = Right(jd, 2) & " " & Mid(jd, 5, 2) & " " & Left(jd, 4)
jt = Left(jt, 2) & ":" & Mid(jt, 3, 2) & ":" & Right(jt, 2)
If DateDiff("s", CDate(jd & " " & jt), CDate(step1_dt)) > 5 Then
If os_process_to_kill <> "N\A" Then
Set o = CreateObject("WScript.Shell")
o.Run "kill -f " & os_process_to_kill, , True
Set o = Nothing
End If
j.Stop
End If
s.DisConnect
Set j = Nothing
Set s = Nothing
================================================
Sample of usage:
suppose you have a job named "My silly job" with this silly T-SQL step:
exec master..xp_cmdshell 'calc.exe', no_output
waitfor delay '555:55:55'
and you want to time-limit this "job" to 10 seconds of running.
Then parameters of its 1st (controlling) step should be:
job_killer_script = "D:\job_killer.vbs"
this_job_name = "My silly job"
time_limit_secs = "10"
os_process_to_kill = "calc.exe"
|
|
Time-limit jobs and stop them automatically