SQL Server Operators – SQL Agent Alert System – Notify Operators

After adding SQL Operators you need to restart SQL Agent

ERROR in SQL Agent Jobs
… NOTE: Failed to notify ‘<operator name>’ via email.

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to
[475] Database Mail is not enabled for agent notifications.

May have log in SSMS > SQL Server Agent > Error Logs

SOLVED
Credit to:
http://sqlblog.jfperry.com/jpsql/post/2009/04/07/NOTE-Failed-to-notify-7boperator7d-via-email.aspx

This was set up on one of my work servers. For a different company this needed to be set up.

On SQL 2005+ you have to tell SQL Server Agent / Properties / Alert System, which profile that you’d like to enable. Here’s what it does.
e.g. and don’t forget to restart SQL Agent

To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.
  2. Right-click SQL Server Agent, and then click Properties.
  3. Click Alert System.
  4. Select Enable Mail Profile.
  5. In the Mail system list, choose Database Mail.
  6. In the Mail profile list, select a mail profile for Database Mail.

Or less likely Transact SQL

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'MyDatabaseProfile'
GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: