October 2, 2008 at 8:51 am
Hello,
I have set up some jobs on SQL 2005 Standard Edition SP2 (9.0.3042) 64-bit, including hourly transaction log backups. I tested out the jobs by setting the Notifications section to send an email to the operator if the job succeeds.
I tested the jobs successfully and switched the Notifications section to send an email if the job fails.
However, I am still getting the email notifications, even though the jobs are succeeding.
Does anyone know why the job is not "listening" to the updated notification setting? How can I correct its behavior?
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 2, 2008 at 11:16 am
I've never seen that not work.
When you open the jobs up in Management Studio and go to Notifications, does it still say "On Completion" or does it say "On Failure"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 9, 2008 at 9:20 am
I may have found out what was happening here.
When one creates a Maintenance Plan using the Maintenance Plan Wizard, one of the last screens gives an option (checkbox) for whether an email report should be sent out for the plan. I checked this originally, but once the plan is created, I don't know where one can go to uncheck the box or undo this choice. I also think it remains in effect even if the corresponding SQL Server Agent job for the plan is set to notify only when the job fails.
I am recreating the plans and will leave this box unchecked. I'll see if that works.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
October 9, 2008 at 12:25 pm
You are correct, the maintenance report setting is different from the job notification setting. The report setting is internal to the maintenance package and has to be changed there, the job status email (complete/success/fail) is handled by the agent and is outside the maintenance package.
Think of it this way - there is a job step created for each subplan in a maintenance package. You could change the job to have additional steps (other additional SSIS packages or SQL Scripts, etc.) and the Job notification would send an email based on the success/failure of the job as a whole - not just one individual step. The report within the maintenanace package is just for that one specific package and is seperate from the job. You could also modify the maintenance package and add a "Notify Operator" task and have it send an email based on individual steps within the maintenance package.
To turn the report off within the maintenance package, look for the report button in the toolbar (looks like a sheet of paper with a graph on it - to the right of "Manage Connections" and the left of "Servers"). Click there and uncheck the "send report to an email recipient" option. Took me forever to find this when it happened to me. 🙂
Good luck!
Chad
October 9, 2008 at 12:53 pm
Great, thanks so much for your help!
My newly created maintenance plans, which I based only on the job email settings, now work correctly. But thanks for the additional information on how to manage the email settings within the maintenance plan packages.
Gratefully,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply