Failed to notify ''operator name'' via email?

I had the same problem where the Test email would work just fine, but the notification at the end of processing a job was failing. The setting was turned on for alerts for "Enable mail profile" but apparently SQL server wasn't seeing this as valid.

My solution was from within management studio:

1) SQL Server Agent (right Click) -> Properties -> Alert System -> Unselect "Enable Mail Profile"

2) restart the SQL Server Agent (right click -> restart)

3) SQL Server Agent (right Click) -> Properties -> Alert System -> Check "Enable Mail Profile" (yes, i know it was on before this process started)

4) Restart the SQL Server Agent again.

After this process, I was successfully able to send notifications to the Operators.

Eric B.

thisguyrox Mr or Mrs. 500 Points: 546 More actions · Answer 8

thisguyrox

Mr or Mrs. 500

Points: 546

May 27, 2010 at 7:45 am

#1173315

It worked Great. Thanks for the post.

Private Gripweed Right there with Babe Points: 772 More actions · Answer 9

Private Gripweed

Right there with Babe

Points: 772

August 30, 2010 at 10:49 am

#1213838

Old thread, but +1.

ysf0306 Valued Member Points: 65 More actions · Answer 10

That was really helpful..this is an amazing forum..Thanks alot!!

MatteoMoretti Ten Centuries Points: 1024 More actions · Answer 11

MatteoMoretti

Ten Centuries

Points: 1024

September 6, 2010 at 2:34 am

#1216799

thanks!! 🙂

JasonYousef SSCommitted Points: 1769 More actions · Answer 12

JasonYousef

SSCommitted

Points: 1769

September 7, 2010 at 10:39 am

#1217439

Henrik, It helped me too! Thanks a Million!

Henrik Staun Poulsen SSCertifiable Points: 6552 More actions · Answer 13

I'm glad it helped, even if this thread is getting a bit old 🙂

Chuck Lathrope Mr or Mrs. 500 Points: 568 More actions · Answer 14

I found one cause for issues for everything but sql agent job alert notifications working. The original DBMail setup stores the default profile in the registry when you setup DBMail for first time. Stupid idea of MS. Anyways, SQL agent reads it only when it starts up. So, if you have a bad profile name in there agent notifications won't work and you won't get any log information other than job failure message: "NOTE: Failed to notify 'OperatorName' via email."

Use this to read the entry:

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile'

Use this to update it:

select 'exec master.dbo.xp_instance_regwrite N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'', N''DatabaseMailProfile'', N''REG_SZ'', N''' + name + ''''

from msdb.dbo.sysmail_profile

where profile_id in (Select profile_id from msdb.dbo.sysmail_principalprofile Where is_default = 1 and principal_sid = 0)

SQL Agent restart is required.

I submitted a Connect item https://connect.microsoft.com/SQLServer/feedback/details/596305/database-mail-configuration please vote it up.

HTH,

-Chuck

@SQLGuyChuck