Operator Emails failing but Tests work...

Question

Operator Emails failing but Tests work...

BillyWilly

SSCrazy

Points: 2304
More actions
September 9, 2003 at 7:08 am

#162352

Hello..
I'm having a problem with scheduled jobs failing and not EMailing the Operator on failure.
1) If I go into "Operators" - my user(I am an operator) properties - and click "Test Email", all works fine and I get the test EMail.
2) If a scheduled job fails, in the job history I get this statement:
"... The last step to run was step 1 (Import_Transactions). NOTE: Failed to notify 'Billy' via email."
Anyone else have this problem ?
I'm using SQL Server 2000:
SQL Enterprise Manager version 8.00.760 (SP3)
SQL-DMO version 8.00.02
-B

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

(1) What happens if you try using xp_sendmail from within T-SQL?

(2) Have you tried using xp_stopmail followed by xp_startmail? I've seen some strange behaviour with SQLMail, and this often cures it, particularly if there's been a problem with the Mail / Exchange server.

Thomas Rushton
blog: https://thelonedba.wordpress.com

BillyWilly SSCrazy Points: 2304 More actions · Answer 2

Thanks Tom-

I usually avoid XP_SendMail because it locks things when using a query as part of the output. Before I try the Stop/Stop Mail commands - what do they actually do ?

And yes - we do occasionally have EMail server problems and this refresh sounds like a possible cure. Will dig into the manuals regarding those 2 commands.

- B

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

quote:
Thanks Tom-

it's Thomas, please! My father-in-law's Tom, and it all gets a bit confusing...

quote:
I usually avoid XP_SendMail because it locks things when using a query as part of the output. Before I try the Stop/Stop Mail commands - what do they actually do ?

xp_stopmail -- stops the SQLMail session

xp_Startmail -- starts the SQLMail session.

quote:
And yes - we do occasionally have EMail server problems and this refresh sounds like a possible cure.

It works for me quite regularly -- we have regular problems with our Exchange server (something to do with it providing Exchange mailboxes for 400 users, and acting as a backup domain controller, and all this on a P133!

It appears as though SQLMail isn't smart enough to try restarting itself (or SQLServer isn't smart enough to try restarting SQLMail, whatever) in the event of an Exchange Server failure.

Thomas Rushton
blog: https://thelonedba.wordpress.com

BillyWilly SSCrazy Points: 2304 More actions · Answer 4

Thanks Tommy ;)-

Will give it a try and see what happens.

Just wanted to make sure that StopMail has no far-reaching nasty side-effects(affects?).

It's just odd to me that the "Operator Test" mailings work fine - why is this even possible?

- B

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 5

quote:
Thanks Tommy ;)-

grr...

quote:
Just wanted to make sure that StopMail has no far-reaching nasty side-effects(affects?).

effects. Should be, but don't blame me!

#include <stddisclaimer.h>

quote:
It's just odd to me that the "Operator Test" mailings work fine - why is this even possible?

Beats me, but I've seen this. Nice to know someone else gets confused like this!

Edited by - ThomasRushton on 09/09/2003 08:17:54 AM

Thomas Rushton
blog: https://thelonedba.wordpress.com

BillyWilly SSCrazy Points: 2304 More actions · Answer 6

Thomas-

Did the StopMail and it said that the sever was not started so it could not be stopped. Then did Startmail and it said it was able to start. Go Figure.

Regarding the other EMail problem/posting - will find my "bug note" from Microsoft and post it on the other posting in a few minutes...

- B

Ramesh Old Hand Points: 375 More actions · Answer 7

When ever the jobs fail to send the notification , first try to send a mail through SQl query analyzer and if it is successful means problem with the agent , stop and start the SQL mail

Don’t know why but it seems that sometime SQL mail agent does not respond in a proper and timely fashion.

Ramesh

marinav SSC Enthusiast Points: 136 More actions · Answer 8

I spent a good 6 month to pin the problem. It appeares this happens on SQL Server first time after Exchange server reboot. After first failure all other SQL job notifications go through fine. Same implies on xp_sendmail. What I ended up doing was a dummy job on each our SQL server that runs right after exchange server reboot (it's scheduled in our company), sends email using xp_sendmail and sends notification. When this job fails (or just notification) all others work just fine. Obviously, none of us (DBAs) wanted 20 emails every morning and we added a rule in OUtlook to delete messages with this specific subject line.

Good luck!