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