Configure Database Mail – MSSQL

In the SSMS expand the “Management”, right click on “Database Mail” and choose “Configure Database Mail” from the context menu

img_595fa90be8132

Click on the “Next” button in the “Welcome” screen

img_595fad201d303

In the “Select Configuration Tasks” screen keep the default “Set up Database Mail by performing the following tasks:” and click on the “Next” button to continue

If the Database Mail is not activated yet the configuration will inform you and you’ll be able to activate it by clicking “Yes”

img_595faa3a3121e

To manually activate the “Database Mail” configure it by executing:

USE master
GO
sp_configure ‘show advanced options’,1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘Database Mail XPs’,1
GO
RECONFIGURE
GO

In the “New Profile” screen enter the profile name (eg. MailProfile1). Then click on the “Add” button to enter the email address and the mail server details

img_595fad96dcbfc

img_595fab65d976a

Click “OK” in the “Database Account” screen, and click on the “Next” button to continue.

In the “Message Profile Security” screen mark the MailProfie ad Public and click on the “Next” button

img_595fac7b6fcff

In the next screens just hit “Next” to continue.

Now we can test with simple body text:

use msdb
go
sp_send_dbmail
@profile_name=’MailProfile1′,
@recipients = ‘someuser@tempdomain.com’,
@body = ‘This is a body text’

Or send a query results by email:

use msdb
go
sp_send_dbmail
@profile_name=’MailProfile1′,
@recipients = ‘someuser@tempdomain.com’,
@body = ‘body’,
@execute_query_database = ‘msdb’,
@query = ‘SELECT subsystem_id FROM syssubsystems’