Configure 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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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' |
If it is disabled, then enable the service broker by executing the below queries
|
1 2 3 4 5 6 7 8 |
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 |
SQL Server Agent service running status
Execute below command to check the status of agent service
|
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.
By default, the database email feature is not enabled. It must be enabled, simply click ‘Yes’
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.
In Account name, provide Account Name, Description (optional). In Email address we are using GMAIL, in Display Name we are using same as Account Name & Reply Email (Optional). In SMTP server and Port Number we are using GMAIL SMTP server & port.
SMTP account will be listed in the SMTP accounts grid view.
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 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.
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.
We can also send a test emails using the T-SQL code. Execute the following T-SQL script
|
1 2 3 4 5 6 7 8 |
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 |
Check your email inbox.
Comments
[…] 1) First Configure Database mail in SQL Server if it is not configured before. You can follow the link for instruction: https://www.ourtechideas.com/blog/configure-database-mail/ […]
[…] 1) First Configure Database mail in SQL Server if it is not configured before. You can follow the link for instruction: https://www.ourtechideas.com/blog/configure-database-mail/ […]