ERROR: Database Mail is not enabled for agent notifications.

I missed a step setting up SQL Agent on a new server. So here it is again.

/*
ENABLE SQL Server Agent to use the Database Mail
*/
-- Make sure SQL Server Agent is running.
EXEC [master].[dbo].[xp_servicecontrol]
 'QUERYSTATE',
 'SQLServerAgent'
 GO

USE [msdb]
--1)
EXEC [msdb].[dbo].[sp_set_sqlagent_properties]
 @email_save_in_sent_folder = 1
 
--2) We want to Use DatabaseMail
EXEC [master].[dbo].[xp_instance_regwrite]
 N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
 N'UseDatabaseMail',
 N'REG_DWORD',
 1

--3) We want to have "SQLMail Profile" be the DatabaseMail profile SQL Agent uses.
EXEC [master].[dbo].[xp_instance_regwrite]
 N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
 N'DatabaseMailProfile',
 N'REG_SZ',
 N'SQLMail Profile'

--
-- STOP/START SQL Server Agent
--
EXEC [master].[dbo].[xp_servicecontrol]
 'STOP',
 'SQLServerAgent'
 GO 
EXEC [master].[dbo].[xp_servicecontrol]
 'START',
 'SQLServerAgent'
 GO

Also see NOTE: Failed to notify ‘operator’ via email.

Comments are closed.

Post Navigation