Configuring and understanding Database mail in SQL Server

Prerequisites to Enable Database Mail

Before Configure Database mail we must enable Service Broker for MSDB database and also need to check SQL Server Agent service running or not.

Check Service Broker status

Execute the following query to check that the service broker is enabled or not.

If it is disabled, then enable the service broker by executing the below queries

SQL Server Agent service running status

Execute below command  to check the status of agent service

If the SQL Server Agent service is ‘Stopped’, start the SQL Server Agent service. Use SQL Server configuration manager to start it.

Or Run ‘services.msc’ to open Services. Search for SQL Server Agent. Right-click on SQL Server Agent and select Start.

Configuring Database Mail by SSMS

In Object Explorer, Expand Management and right-click the select Configure Database Mail

Welcome screen, Click Next

Select the Setup Database Mail by performing the following tasks radio button and click Next.

Enabling the database email feature

By default, the database email feature is not enabled. It must be enabled, simply click ‘Yes’

Creating a profile

In Profile name provide a profile name, here we have given ‘SQL_DB_Email’. The description is optional and click the Add button to create an SMTP account.

Creating an account

In Account name, provide Account NameDescription (optional). In Email address we are using GMAIL, in the Display Name, we are using the same as Account Name & Reply Email (Optional). In SMTP server and Port Number, we are using GMAIL SMTP server & port.

Adding profile and account

SMTP accounts will be listed in the SMTP accounts grid view.

Configure the database email profile public

Click Next to configure the database mail profile security. We will configure the database email profile public. Any database user or role can send emails using Public profile. Users must be created in the MSDB database, and those users must be a member of a database role named DatabaseMailUserRole to send an email. We can also make the profile as a default profile. Once profile security sets, click Next to configure the system parameter.

Configure System Parameter

Configure System Parameter window, we can set a number of retry attempts, maximum attachment file size, list of file extensions that are prohibited from sending as an attachment.

Review the entire configuration and list of actions. Click Finish to complete.

You may view the report, then close.

Sending test email using SSMS

To send a test email using SSMS, Open SQL Server Management Studio >> Expand Management in SQL Server Management >> Right-click  Database Mail and select Send Test Mail.

In the Database Mail Profile drop-down, select the name of the profile used to send an email (Database Mail Profile), In the To text box, provide the email address of the email recipient and click Send Test Mail.

Click ‘OK’

Check your email inbox.

Send test mail using the T-SQL

We can also send test emails using the T-SQL code. Execute the following T-SQL script

Check your email inbox.

Configuring Database Mail by TSQL

Enabling the database email feature

By default, the database email feature is not enabled. It must be enabled, simply execute the below TSQL script. In the SQL Server Management Studio, execute the following statement.

Enable/disable advanced options

Enable/disable Database Mail XPs

Creating a profile by TSQL

To create “MyMailProfile” using the sysmail_add_profile procedure to create a Database Mail profile. Execute the statement below.

Grant the Database Mail profile access to the msdb public database role

Now use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile. Please execute the statement below.

Creating account by TSQL

Now Account can be enabled by using the sysmail_add_account procedure. We are going to create the account, “Gmail” using ‘smtp.gmail.com’ as the mail server and a ‘valid e-mail address as the e-mail account. Please execute the statement below.

Adding profile and account by TSQL

Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account with the Database Mail profile. Please execute the statement below.

Deleting profile and account

So, now database mail configuration is done successfully. If for any reason any above steps return an error, use the below TSQL to roll back the changes.

Check that the existing email profile

To check that the existing email profile is configured on the server.

Database Mail queues

To check the status of Database Mail queues.

To start Database Mail queues

To stop Database Mail queues

To find failed mails

TSQL to find all sent mail

 

Facebook Comments Box

Comments

Leave a Reply

Your email address will not be published.