SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Time-limit jobs and stop them automatically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 03/15/2005 :  09:12:19  Show Profile  Visit Stoad's Homepage  Reply with Quote
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"

Edited by - Stoad on 03/19/2005 21:39:40
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.24 seconds. Powered By: Snitz Forums 2000