Configuring Database Mail.

Problem: How to configure Database Mail.?

Solution: If we need to send alerts, notifications, Job status of a SQL Server agent from SQL Server Database. You may have to configure Database Mail to send e-mail message to concerned persons as it keeps notifying when there is a fatal error or failure of a SQL job. So, it keeps you aware of the process even though you are not on the computer.

You can configure query results into an attachments and also in the body, also configure subject of email, etc.

To do this we have few things to do.

·         Enable Database Mail

·         Create Database Mail account(sysmail_add_account_sp)

·         Create Database Mail Profile(sysmail_add_profile_sp)

·         Adding the Account to Mail profile(sysmail_add_profileaccount_sp)

Example:

In this article I am going to show you we can create a mail account, profile and add the account to profile using T-SQL, also view the results after configuring.

Step 1: enabling the database using sp_configure to set the server configurations.

EXEC sp_configure 'show advanced options', '1';

RECONFIGURE

GO

EXEC sp_configure 'Database Mail XPs', 1;

RECONFIGURE

GO

SELECT *

FROM sys.configurations where name='Database Mail XPs';

GO

Step 2: Creating a database mail account, passing parameters to the system stored procedure to configure from which the mail needs to be send and also the server from where it sends the email from.

-- Create a Database Mail account 

EXECUTE msdb.dbo.sysmail_add_account_sp 

    @account_name = 'DBMail', 

    @description = 'Mail account for use by all database users.', 

    @email_address = 'learnersSQL@gmail.com', 

    @replyto_address = 'LearnersSQL@gmail.com', 

    @display_name = 'Database Automated Mailer', 

    @mailserver_name = 'smtp.gmail.com,

@port=587; 

select * from dbo.sysmail_account

OUTPUT:

account_id	name	description	email_address	display_name	replyto_address
3	DBMail	Mail account for use by all database users.	learnersSQL@gmail.com	Database Automated Mailer	LearnersSQL@gmail.com

Step 3: Creating a mail profile to hold the mail account and for sending the mail we need a profile.

-- Create a Database Mail profile 

 EXECUTE msdb.dbo.sysmail_add_profile_sp 

    @profile_name = 'DB Mail Public Profile', 

    @description = 'Profile used for DB Alerts mail.' ; 

 

 select * from dbo.sysmail_profile

OUTPUT:

profile_id	name	description	last_mod_datetime
3	DB Mail Public Profile	Profile used for DB Alerts mail.	2/24/2017 16:37

Step 4: adding the account to the profile. Both the profile and the account must already exist. Otherwise, the stored procedure returns an error.

-- Add the account to the profile 

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 

    @profile_name =  'DB Mail Public Profile', 

    @account_name = 'DBMail', 

    @sequence_number =1 ; 

select * from dbo.sysmail_profileaccount

OUTPUT:

profile_id	account_id	sequence_number	last_mod_datetime
3	3	1	2/24/2017 16:37

In the next article let’s explore on how to send an email from DB engine using the profile.