Select Page

SQL Server Database Mail plays a nice role in my administration. I have set up a few SQL Server Agent Jobs that calls upon stored procedures that I put together to help monitor issues that arise with the data. Sometimes its specific for tracking GIS Schema changes or incoming dirty data from interfacing systems. I like to be in the know right away as it helps me address problems quickly.

First thing to understand is the fact that in order to use DB Mail you need the SQL Server Agent running. Secondly in order for you to execute the system stored procedure sp_send_dbmail you need to be a member of the DatabaseMailUserRole which resides in MSDB.

Step 1: Show Advanced Options

use [master]
go
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

Side note: some options require a restart of the database engine however some can be circumvented by supplying the argument “with override” to the reconfigure command. For more information please visit the following MSDN posts: Setting Server Configuration Options and RECONFIGURE (Transact-SQL).

Create the Mailer Profile

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DB Admin', -- Can be whatever you specify
@description = 'Profile used for database mail'

Create the Mailing Account

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Notifier', -- Can be whatever you specify
@description = 'Notification Account of Database Changes',
@email_address = 'No-Reply@sqlsam7.com', -- can be fictitious
@display_name = 'No-Reply',
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@username = 'YourAccount@gmail.com',
@password = 'your password',
--@use_default_credentials =, 
@enable_ssl = 1

Associate the Mailer Profile to the Mailing Account

EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DB Admin', -- Must be the same as above
@account_name = 'Notifier', -- Must be the same as above
@sequence_number = 1

Test

USE [msdb]
EXEC sp_send_dbmail
@profile_name = 'DB Admin', -- Can be whatever you specify
@recipients = 'samson@sqlsam7.com',
/* @copy_recipients = '[Email address protected]
@blind_copy_recipients = '[Email address protected] */
@subject = 'Test Email from SQL Server dbMail',
@importance = 'High',
@body = 'This is a test of the SQL Server dbMail.',
@body_format = 'Text' -- Can be HTML too.

Verify Mail Log and Event Log

SELECT * FROM msdb.dbo.sysmail_allitems
ORDER BY mailitem_id DESC
SELECT * FROM msdb.dbo.sysmail_event_log 
ORDER BY log_id DESC
Share This