October 24, 2014 at 11:23 am
Years ago, when I first started working with SQL Server 2000, our jobs were structured like this:
Step 1: Do something (on success->go to step 3; on failure->goto step 2)
Step 2: Error: Do Something (create application alert. on completion go to step 3)
Step 3: Do this thing (on success->go to step 5; on failure->goto step 4)
Step 4: Error: Do this thing (create app alert. on completion go to step 5)
Step 5: Do that thing (on success -> quit with success; on failure go to step 6)
Step 6: Error: Do that thing (create app alert. on completion quit with failure.)
Note: "create application alert" refers to an in-house application and has nothing to do with db mail alerts or notifications. The application alerts has been overused by the application such that no one monitors or even reads those alerts. It's used like an error log for researching into the why of a problem.
In an effort to catch problems before the end users notice them, I have been rewriting Dts/SSIS packages and more recently I configured DBMail and setup alerts with our sql jobs on the SQL 2008 and 2012 servers. I was hoping to get an email about a job failure when it happened. Unfortunately, I have just discovered that the alerts/notices only work if the job quits and the last step reports a failure. So... I can break the job up into many jobs just having 1 step, but I would rather keep similar actions together. Also, while my my steps are usually stand alone, I also have steps that need to run after previous steps regardless of their completion status. If these steps are broken up into multiple jobs, I'm not sure how I can enforce the "one at a time" environment that I have now.
So, in summary, my goals are:
1. utilize built in alerting/notices on job failures. (minimal custom coding and/or alerting solutions)
2. avoid complexity
Is it even possible to setup a job so that it fails when a early step fails but successive steps succeed?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply