October 30, 2003 at 3:18 pm
Hi All,
For one of my SQL Servers, I got a new requirement that the business DO NOT want to run one specific job, e.g. datapopulatejob1 (which populates data) on given dates. They gave me all these dates on which they don't want this job to run. On the back ground, they were doing some special processing from the applications, hence we don't receive out input file (automated). So my job will fail on those dates & page me. Thats the reason they don't want to run on those dates.
Here is the way I did it:
1. I copied these dates into an Excel.
2. With DTS, I imported that into table1.
3. Is a separate job (scheduled 30 mins before datapopulatejob1), Checking if current date is in this table1. If it is then disable the datapopulatejob1.
Please let me know if there is any other best way to handle this request.
Thanks & Regards,
Murali Damera
.
October 30, 2003 at 4:58 pm
1) Disable the job
2) Import the dates into a table
3) Develop a routine to call the job in a SP by checking the dates.
Shas3
October 30, 2003 at 10:15 pm
If you know which dates you won't receive a file why don't you make your error trap smarter by stating:
IF error = no file AND NOT IN (SELECT Dates FROM
) THEN page ELSE ignore and quit showing success???
AJ Ahrens
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 31, 2003 at 2:46 am
Your original solutions seems OK. The only problem could be if your job that disables the datapopulatejob1 is stuck for some reason, the datapopulatejob1 might never run. And you won't get paged because of the failure of the enabling/disabling job...
That's why I think the solution of whoteegan is better.
October 31, 2003 at 5:24 am
I have another suggestion.
Let the job which checks for the date control the execution of your main job.
If not exists ( select date from [table1]
exec sp_start_job .....
Else
Print "Job is Not scheduled for today"
select getdate()
End
Make sure your schedule in the original job is disabled and the above controlling job is run daily.
October 31, 2003 at 5:30 am
eemmmhhh....
True.. We had this situation earlier. I am still argueing myself with the following options:
1. My optoin.
2. Shas3's option
I am somehow not feeling comfortable to transfer control back to SPs.
I am hoping that I could do this with Schedular better.
Thank You to all for your input.
.
October 31, 2003 at 5:33 am
oooops.... I did't see tejeshc's response... I was typing by then....
This seems to be a nice idea, except that I am going to have an extra job getting added to my 112 jobs. huh!!!
Thank You tejeshc for your input.
.
October 31, 2003 at 6:02 am
I have a couple of options for you,
In the job add a step using an ActiveX script option and check the date. if the date falls into your "do not run" range, fail the step, exit the job.
option2:
in the dts package, add an activeX script to check for the existance of the file. If the file exists, run your process, if not fail the step, exit the DTS package.
Both of these would fail the job, so you could also add in an activex task to handle paging you when a failure occurs that you are not expecting.
October 31, 2003 at 10:57 am
Can your job process empty input files without giving errors?
If so, here is an idea. :
If the users can send you an empty file on the days of special processing, you do not have to change your schedule. Your job will terminate successfully after procesing zero records.
This is how the mainframe jobs are scheduled (at a place I used to work).
Regards
October 31, 2003 at 11:12 am
No. Cannot do this because we receive this file from the client's system automatically pushed into a seperate folder. This will be picked by my job, process then move to archieve folder.
.
November 2, 2003 at 5:59 pm
Hi, if you are not receiveing a file on the days you want the job not to run, then I would simply have a first step of checking for the file. If it exists, carry on to the originakl first step as per normal (indicating a failure if the job fails at a later stage), if a file does not exists, goto another step that will send an email to yourself to indicate that no file was there and then quit with success - this email step will only been referenced if the file does not exist. Hope this makes sense to you - I knoqw it works as I do the same thing on at least 2 of our servers. The clioent only wants the data loaded when they supply a file (at very irregular date/times).
cheers
November 2, 2003 at 10:14 pm
KISS it. I vote for tejeshc's solution.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply