Database Mail Works but Alerts Do Not

Question

Database Mail Works but Alerts Do Not

Buxton69-502449

SSCrazy

Points: 2026
More actions
November 2, 2007 at 10:10 am

#118710

I've tried everything, the Database Mail is configured, the test works, the operator is set up and if a job fails it gets alerted but if I cause an error for which an alert should be sent then I get nothing and the error logs show nothing, I've tried reconfiguring it all over again and I get the same thing, I've made sure all the alerts are enabled and the operator can receive alerts. I've set the alerts for a server event level by severity and also some by error number. Has anyone got any suggestions, I've searched all over for the answer and most of the forum answers are restart SQL Agent but my mail works!!!
To recapp:
Database mail works.
Job notifications work.
Alerts don't work.
Just to add that this all works fine in 2000, it's just my 2005 server I'm having problems with.
I just did a test using RAISEERROR (50603, 10, 1) and the alert works and sends me an email but it still doesn't work when the job runs that kicks off this error, in the send history it says I was alerted via net send but I don't have this enabled anywhere, any ideas?
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

MIJ SSCommitted Points: 1915 More actions · Answer 1

You might start by looking at the Database Mail Profile used by SQL Agent (Agent Properties --> Alert System). Change it to use your default global mail profile and see if that fixes the problem. Even if that's not the mail profile you ultimately want to use, it could identify the problem area.

Also, I think there are sometimes cases where "RAISERROR(...) WITH LOG" will trigger an alert when "RAISERROR(...)" alone will not, but I don't know if that has any bearing on your situation.

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 2

When you say default global mail profile what do you mean, I've tried reconfiguring this a few times but each time I get the same results. I'm using the progile that was set to default when Database Mail was configured.

On the RAISERROR, I'm not using the with log part so I don't think that is the error I'm getting.

Thanks

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 3

I added a different alert to be set off by a different error number and this works, appart from the error number the alert is configured exactly the same, even the alert in sys.messages is the same appart from the wording, it has the same error level, which is 16, and has 1 for is_event_logged!!! It's getting very annoying! I deleted the original alert and recreated it and restarted SQL Agent.

Has anyone got any idea? The server is still on SP1, could this make a difference?

Thanks

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 4

One thing to try, within SQL Server Agent move to Alert System and Ensure that the Enable Mail Profile is checked.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 5

It is, it just appears to be certain alerts, I think I might delete them all and recreate them.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 6

In the sp2 fixlist ( http://support.microsoft.com/kb/921896 )

there is a fix included regarding agent and alerts. Maybe that's the issue.

Check it out and if you can upgrade to sp2. Don't forget to also implement upto the latest cumulative hotfix for sp2 (afaik now it is CU4)

fix 926612 (http://support.microsoft.com/kb/926612/) FIX: SQL Server Agent does not send an alert quickly or does not send an alert when you use an alert of the SQL Server event alert type in SQL Server 2005

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 7

Thanks, I think I fixed it by removing the ones that wouldn't work and creating new ones however I don't trust it to work reliably as it seemed like such a random error, I think SP2 will be applied very soon.

Thanks

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 8

"FIX: Error message when you try to use Database Mail to send an e-mail message in SQL Server 2005: "profile name is not valid (Microsoft SQL Server, Error 14607)" is corrected in the latest cumulative update for SP2.

http://support.microsoft.com/kb/934226/

http://support.microsoft.com/kb/937137

Tommy

Follow @sqlscribe

chief.warchief SSC Enthusiast Points: 100 More actions · Answer 9

Just wanted to contribute to a thread that lead to a solution for me.

I had the same problem with the OP, where database mails and all email within SPs are working fine, but not receiving emails from Alerts.

Don't forget that you need to configure Alert System under SQL Server Agent.

All credits go to the below source, which solve my issue.

http://www.mssqltips.com/tip.asp?tip=1523

Particularly, the following section:

"Note: This step is often overlooked when creating an alert for the first time. SQL Agent must be setup correctly for operators to receive an alert e-mail."

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks chief. That is quite helpful.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

chief.warchief SSC Enthusiast Points: 100 More actions · Answer 11

No problem mate.

Glad it's useful to others.

Hopefully it'll save some times and frustration for others.

Took me half a day yesterday to research/figure out what was wrong with it.

Thanks.

Regards.

DancesWithChickens SSC Enthusiast Points: 163 More actions · Answer 12

Thanks for this. I ran across the thread while troubleshooting agent notifications and enabling mail in the agent itself did the trick. I appreciate your help.

Sunny1 SSC Eights! Points: 871 More actions · Answer 13

Thanks for the solution! For some reason, the Mail profile in Mail Session of the my SQL Agent Alert was not set. I set the Mail profile in the SQL Server Agent properties and the problem was fixed. 🙂