March 21, 2011 at 12:09 pm
I have some jobs that has about 12 steps and i want the steps to continue EVEN if they fail. I know i can make the entire job stop if i tell it to end upon failure etc...i WANT it to continue upon failure BUT i also want the job to report as FAIL if any of the steps fail inside the job. Is there a way to do this? I have many many jobs with multiple steps (7 or 8 plus steps)... If the job completes successfully then ofcourse i want things to move on normally.
Any ideas?
DHeath
thanks for taking the time to read my post
DHeath
March 21, 2011 at 12:57 pm
Under the advanced tab in Job Step properties, can't you set the On Faiilure Action to "Go to the Next Step?" The last step of course needs to be either "Quit the job reporting Success" or "Quit the job reporting failure"
?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 21, 2011 at 1:02 pm
Yes... i understand the go to next step aspects... but in the middle of a job ,,,,lets say step #5 of a total of 8 steps fail of a particular job and ALL others complete successfully.. I want the job to report to me as a failed job, yet all other steps completed successfully.
hopefully that expalins a bit beter 🙂
DHeath
DHeath
March 21, 2011 at 1:45 pm
What you'll want to do is to make the last step of the job look at the result of the previous steps and then either raise an error to fail the step/job or pass the step/job. You can make this dynamic too using a stored procedure and SQL Agent Tokens in case you think you may need this behavior within multiple jobs.
Job flow goes something like this:
1. On Success and Failure: Goto Next Step
2. On Success and Failure: Goto Next Step
.
.
.
n. On Success Quit Job Reporting Success / On Failure Quit Job Reporting Failure
Last step code does something like this:
1. Use (JOBID) token to find whether any steps in the job [on this run] reported failure.
1a. If any failed raise an error to make this step fail, and thus the job.
1b. If none failed happily exit the routine so the job can succeed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 21, 2011 at 3:02 pm
opc...
that is exactly what i am looking for but not understanding how to put this in effect. Was trying the use of tokens and not complete up to par on them so i am sure at this moment its a user error ( me :w00t: ). i was trying to look in sysjobs but it only records the last step so i think i am missing something along the lines. I know i cant be the only person that has come up against this and wondering if i need to re-invent the wheel or is some code already out there.
DHeath
ps.. to all that have took time to read my thread ... replied or not Thank you for your time
DHeath
March 21, 2011 at 3:49 pm
The Agent tables are not my favorite thing in the world to dig through 😀 The way dates are stored, the lack of an easy way to find a job's current execution status directly from the tables, the Step 0 garbage they stuff into the dbo.sysjobhistory...the list seems endless when I get in there looking to get at something. When I go down the rabbit hole I can always get what I need, but by the time I need it again I have forgotten everything about my last effort. Here is a link to the tables you'll be dealing with to get your info:
http://msdn.microsoft.com/en-us/library/ms181367.aspx
I think you want msdb.dbo.sysjobsteps.last_run_outcome.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 21, 2011 at 4:23 pm
Thanks for the info.... uuuggggg i dont enjoy digging this deep in especially when i have not been there in a good little while but the challenege is always fun =]. I see what you are talking about in the msdb and sysjobs or sysjobhistory even then it doesnt give me a clean explaination but i do get a better one. I would post a pic but not sure how hehehe O well thanks for pointing me in the right direction and your time is greatly appreciated.
I will figure it out some way ..some how 😉 Thanks again
DHeath
DHeath
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply