How to Configure SQL server Database mail and send mail using SQL Database Mail

Sandeepraturi
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 1409 red flag
Rating: 3 out of 5  
 1 vote(s)

here, we will learn how we can configured SQL Database Mail in SQL Server and how to send mail using SQL Server.


 Download source code for How to Configure SQL server Database mail and send mail using SQL Database Mail

Introduction

Here i am going to be explain how we can Setup or Configure SQL Server Database Mail. And we will try to send SQL Query Output in mail via using SQL Database Mail.

Background

Below key points will help us to Configure mail setup in SQL Servers:

  • We will use System Default Database MSDB, because all mail configuration information maintained in it.
  • sp_send_dbmail store procedure : This is System default SP & store all mail features. And we can change features as per our need.

Description with Script

In this Article we are going to be configured SQL SERVER Database Mail. It help us to send mail alert to SMTP servers. Help of this we can send of query output in mail. It has lots of features but here i only write about send SQL query output as in mail. 

How to configure SQL Database Mail Alert:

Steps:-1
Open SQL Server and explore the Management Tab, then right click on Database Mail and click on Configure Database Mail link Screen shot as below for your reference.


Step:2-


Step:3



Step:4
Here we will Add profile Name for Database Mail, screen shot as below:-



Step:5
Here, we will write SMTP details for mail id's, write sender mail id details here.



Step:6
Now your profile has been created now click on Next button to configure more features.



Step:7
Tick Yes on default profile as below screen shot then click on Next button.



Step:8
It is optional, you can set Retry attempts value in Send mail failure case then click on Next button.



Step:9



Step:10



Now Database Mail profile has been configured successfully, so we will test DB mail profile is working or not??

Step:1

Right click on Database Mail and click on Send Test E-Mail



Step:2
Select DB Profile and Write receiver mail id and click on send Test E-Mail.



Step:3
Click on OK button


Step:4
Now check your mail, mail received successfully. Output as below




Now we will send SQL Query Output as in Mail:-

Before going to start remember below points:-
  • Here we will use System Default Database MSDB, because all mail configuration information maintained in it.
  • sp_send_dbmail store procedure : This is System default SP & store all mail features. And we can change features as per our need.
Script as below:-
  • Change Profile name which you were entered at the time of Database mail Configuration.
  • Change Recipients mail id's, you can enter multiple mail id's using ; separator.
----------------Database Mail Sent Sript-------------------------
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @Table NVARCHAR(MAX) = N''

SELECT @Table = @Table +'<tr style="background-color:'+CASE WHEN (ROW_NUMBER() 
OVER (ORDER BY [CustomerID]))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END +';">' +
'<td>' + CAST([CustomerID] AS VARCHAR(100))+ '</td>' +
'<td>' + [ContactName]+ '</td>' +
'<td>' + [City]+ '</td>' +
'</tr>'
FROM Test.dbo.Customers
 
SET @tableHTML = 
N'<H3><font color="Black">Till Date Registered Data</H3>' +
N'<table border="1" align="left" cellpadding="2" cellspacing="0" 
style="color:black;font-family:arial,helvetica,sans-serif;text-align:left;" >' +
N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;">
<th>CustomerId</th>
<th>CustomerName</th>
<th>City</th>
</tr><tr>'
 + @Table + N'</table>' 
 
EXEC msdb.dbo.sp_send_dbmail--- Use MSDB database
@profile_name = 'DBAlert',--- Write your Mail Profile which was you configured in Database Mail
@recipients = 'abc@gmail.com;', -- Write Recipient Email Id's, you can write multiple email id's with using ; seprate
@body = @tableHTML,
@body_format = 'HTML' ,
@subject='Auto Mail Alert';--- set Subject for Alert

Script screen shot as below:



After execution of above script, check your mail. Output as below:-


Conclusion

  • It is very easy to use.
  • It help us to send direct mail from SQL server.

Reference

You can read more about DB Mail from Microsoft Site, link as below
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

Page copy protected against web site content infringement by Copyscape

About the Author

Sandeepraturi
Full Name: Sandeep Raturi
Member Level: Starter
Member Status: Member
Member Since: 2/28/2012 10:43:45 AM
Country: India
Thanks & Regards Sandeep Raturi(Sr. software engineer) Email: s.raturi89@gmail.com
http://www.dotnetfunda.com/profile/sandeepraturi.aspx
Senior Software Engineer

Login to vote for this post.

Comments or Responses

Posted by: Laurielewis042 on: 8/31/2018 | Points: 25
very nice I am impressed
Posted by: Sandeepraturi on: 8/31/2018 | Points: 25
thank you laurielewis
Posted by: Collinsjordan on: 9/19/2018 | Points: 25
I don't know the reason, but I can't do it, Maybe I have another version?
Posted by: Sandeepraturi on: 9/19/2018 | Points: 25
Version is not a problem maybe your system firewall not allowed to send mail. You just stop firewall setting then try.
Posted by: Eddie007 on: 10/8/2018 | Points: 25
To share this update, I'm here to connect Microsoft Help Windows 10 support easily, to give you a Batter feature, on https://windowsclassroom.com/how-to-get-help-in-windows-10 and click on More Version Edition Go to the site for

Login to post response

Comment using Facebook(Author doesn't get notification)