SQLAgent: Test email an operator

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