Configuring Database Mail

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
Advertisements

8 thoughts on “Configuring Database Mail

  1. Kelly says:

    I have a question about the @Importance. Should this carry over to the importance level on the email server of the recipient? I am trying to make the recipient get a ‘high importance’ email from our server but it isn’t working.

  2. I had been curious about if you ever thought of modifying the page layout of your blog? Its well written; I enjoy what youve got to state. But maybe you could include a little more in the way of written content so people could connect to it better. You have got a great deal of text for only having one or two photos. Maybe you could space it out better?

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