Author Archives: John Perry

Create an operator

USE [msdb]
GO

EXEC [msdb].[dbo].[sp_add_operator]
    @name = N'DBA',
    @enabled = 1,
    @weekday_pager_start_time = 90000,
    @weekday_pager_end_time = 180000,
    @saturday_pager_start_time = 90000,
    @saturday_pager_end_time = 180000,
    @sunday_pager_start_time = 90000,
    @sunday_pager_end_time = 180000,
    @pager_days = 0,
    @email_address = N'TheDBAEmail@mycompany.org',
    @category_name = N'[Uncategorized]'
GO

Then test it.

--Send email to an operator called, "DBA', using the profile you created called, "SQLProfile".
USE [msdb];
GO

EXEC [dbo].[sp_notify_operator]
    @profile_name = N'SQLProfile',
    @name = N'DBA',
    @subject = N'Test Notification',
    @body = N'This is a test of notification via e-mail.';
GO

Also check to see if it was sent. Sometimes takes 10-15 seconds to appear…

SELECT  TOP 10 *
FROM    [msdb].[dbo].[sysmail_sentitems]
ORDER BY 1 DESC

And while we are here let’s make ourselves the failsafe operator.

EXEC [master].[dbo].[sp_MSsetalertinfo]
    @failsafeoperator = N'DBA'
GO 
EXEC [master].[dbo].[sp_MSsetalertinfo]
    @notificationmethod = 1
GO 

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.