Spread the love

Configuring 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.

USE master 
go 
 
SELECT database_id AS 'Database ID', 
       NAME        AS 'Database Name', 
       CASE 
         WHEN is_broker_enabled = 0 THEN 'Service Broker is disabled.' 
         WHEN is_broker_enabled = 1 THEN 'Service Broker is Enabled.' 
       END         AS 'Service Broker Status' 
FROM   sys.databases 
WHERE  NAME = 'msdb'

USE master

SELECT database_id AS 'Database ID',

NAME AS 'Database Name',

CASE

WHEN is_broker_enabled = 0 THEN 'Service Broker is disabled.'

WHEN is_broker_enabled = 1 THEN 'Service Broker is Enabled.'

END AS 'Service Broker Status'

FROM sys.databases

WHERE NAME = 'msdb'

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

Use master
go
alter database [MSDB] set single_user with rollback immediate
GO
alter database [MSDB] set Enable_Broker
GO
alter database [MSDB] set multi_user with rollback immediate
GO

Use master

alter database [MSDB] set single_user with rollback immediate

alter database [MSDB] set Enable_Broker

alter database [MSDB] set multi_user with rollback immediate

SQL Server Agent service running status

Execute below command to check the status of agent service

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

1	EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

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 Name, Description (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

use MSDB
go
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL_DB_Email'
  , @recipients = 'sanjay.humania@gmail.com'
  , @subject = 'Automated Test Results | Date: 17/11/2019 (Successful)'
  , @body = 'Hi please visit https://www.ourtechideas.com.'
Go

use MSDB

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQL_DB_Email'

, @recipients = 'sanjay.humania@gmail.com'

, @subject = 'Automated Test Results | Date: 17/11/2019 (Successful)'

, @body = 'Hi please visit https://www.ourtechideas.com.'

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 advanced options

sp_configure 'show advanced options', 1; -- enable or disable ( 0 or 1)
GO
RECONFIGURE;
GO

-- Enable / disable advanced options

sp_configure 'show advanced options', 1; -- enable or disable ( 0 or 1)

RECONFIGURE;

Enable/disable Database Mail XPs

-- Enable / disable Database Mail XPs

sp_configure 'Database Mail XPs', 1; -- enable or disable ( 0 or 1)
GO
RECONFIGURE
GO

-- Enable / disable Database Mail XPs

sp_configure 'Database Mail XPs', 1; -- enable or disable ( 0 or 1)

RECONFIGURE

Creating a profile by TSQL

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

-- Create a Database Mail profile  

EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'Notifications',  -- Profile name
    @description = 'Profile used for sending outgoing notifications using Xmail.' ;  -- Description
GO

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'Notifications', -- Profile name

@description = 'Profile used for sending outgoing notifications using Xmail.' ; -- Description

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.

-- Grant access to the profile to the DBMailUsers role  

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'Notifications',  -- Profile name
    @principal_name = 'public',  
    @is_default = 1 ;
GO

-- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'Notifications', -- Profile name

@principal_name = 'public',

@is_default = 1 ;

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.

-- To create a new Database Mail account holding information about an SMTP account
-- Create a Database Mail account  

EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'Gmail',  
    @description = 'Mail account for sending outgoing notifications.',  
    @email_address = 'Use a valid e-mail address',  -- Use a valid e-mail address from where mail will be send
    @display_name = 'Automated Mailer',  
    @mailserver_name = 'smtp.gmail.com', -- SMTP
    @port = 465, -- Port
    @enable_ssl = 1,
    @username = 'Use a valid e-mail address', -- Use a valid e-mail address from where mail will be send
    @password = 'Use the password for the e-mail account above' ;  
GO

-- To create a new Database Mail account holding information about an SMTP account

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Gmail',

@description = 'Mail account for sending outgoing notifications.',

@email_address = 'Use a valid e-mail address', -- Use a valid e-mail address from where mail will be send

@display_name = 'Automated Mailer',

@mailserver_name = 'smtp.gmail.com', -- SMTP

@port = 465, -- Port

@enable_ssl = 1,

@username = 'Use a valid e-mail address', -- Use a valid e-mail address from where mail will be send

@password = 'Use the password for the e-mail account above' ;

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.

-- Add the Database Mail account to the Database Mail profile
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'Notifications',  
    @account_name = 'Gmail',  
    @sequence_number =1 ;  
GO

-- Add the Database Mail account to the Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'Notifications',

@account_name = 'Gmail',

@sequence_number =1 ;

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.

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'Notifications'
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Notifications'
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Gmail'
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notifications'

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'Notifications'

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'Notifications'

EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Gmail'

EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Notifications'

Check that the existing email profile

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

-- Check that the e-mail profile is configured 

SELECT [sysmail_server].[account_id],
       [sysmail_account].[name] AS [Account Name],
       [servertype],
       [servername] AS [SMTP Server Address],
       [Port]
FROM [msdb].[dbo].[sysmail_server]
     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

-- Check that the e-mail profile is configured

SELECT [sysmail_server].[account_id],

[sysmail_account].[name] AS [Account Name],

[servertype],

[servername] AS [SMTP Server Address],

[Port]

FROM [msdb].[dbo].[sysmail_server]

INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

Database Mail queues

To check the status of Database Mail queues.

-- Displays the status of Database Mail queues.

EXECUTE msdb.dbo.sysmail_help_status_sp ;  
GO

-- Displays the status of Database Mail queues.

EXECUTE msdb.dbo.sysmail_help_status_sp ;

To start Database Mail queues

-- To start Database Mail queues

USE msdb ;  
GO  
EXECUTE dbo.sysmail_start_sp ;  
GO

-- To start Database Mail queues

USE msdb ;

EXECUTE dbo.sysmail_start_sp ;

To stop Database Mail queues

-- To stop Database Mail queues

USE msdb ;  
GO   
EXECUTE dbo.sysmail_stop_sp ;  
GO

-- To stop Database Mail queues

USE msdb ;

EXECUTE dbo.sysmail_stop_sp ;

To find failed mails

-- failed mailserver_name

use msdb
go

SELECT * FROM sysmail_faileditems   
ORDER BY last_mod_date DESC

-- failed mailserver_name

use msdb

SELECT * FROM sysmail_faileditems

ORDER BY last_mod_date DESC

TSQL to find all sent mail

--  to find sent mail

use msdb
go

select recipients , subject , convert(VArchar(12) , send_request_date , 103) [Sent Date],
CAST(send_request_date AS time(0)) [Sent Time] ,
sent_status from sysmail_allitems where cast(send_request_date as date) = '06-july-2022' -- date
--and subject like --'%Mail Sent Through alertsprofile%' -- subject line
--And recipients like '%sanjay%humania%' -- recipients name/text

order by CAST(send_request_date AS time(0)) desc

-- to find sent mail

use msdb

select recipients , subject , convert(VArchar(12) , send_request_date , 103) [Sent Date],

CAST(send_request_date AS time(0)) [Sent Time] ,

sent_status from sysmail_allitems where cast(send_request_date as date) = '06-july-2022' -- date

--and subject like --'%Mail Sent Through alertsprofile%' -- subject line

--And recipients like '%sanjay%humania%' -- recipients name/text

order by CAST(send_request_date AS time(0)) desc

Spread the love

Configuring Database Mail in SQL Server

Configuring Database Mail in SQL Server

Check Service Broker status

SQL Server Agent service running status

Configuring Database Mail by SSMS

Enabling the database email feature

Creating a profile

Creating an account

Adding profile and account

Configure the database email profile public

Configure System Parameter

Sending test email using SSMS

Send test mail using the T-SQL

Configuring Database Mail by TSQL

Enabling the database email feature

Enable/disable advanced options

Enable/disable Database Mail XPs

Creating a profile by TSQL

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

Creating account by TSQL

Adding profile and account by TSQL

Deleting profile and account

Check that the existing email profile

Database Mail queues

2 thoughts on “Configuring Database Mail in SQL Server”

Leave a Reply Cancel reply

Categories

Recent Posts

Recent Comments

Archives

Advertisement