You may face a case where you need to send an email with some summary information based on certain DB queries that you have in place. The best presentation of this is to present the data in tabular way in your email. In the past, to satisfy such a requirement you had to build your own .NET or Java code and write long algorithm to accomplish this. Which is a time consuming for such a small requirement… don’t agree?
Now you can send emails from SQL server in tabular format it in very easy way using SQL server mail feature. Within couple of lines of SQL statements you can send a very good looking email to your recipient list… below is a summary of steps of how you can do it:
- Build a temporary table on your DB to hold your result sent.
- Populate the temporary table with your summary data records.
- Do some sort of conversion to convert your table data into an XML.
- Build your HTML header and concatenate the result of step 3 into your body html tag
- Send the email using SQL server mail.
Below is a sample code to send emails from SQL server in tabular format, it is basically send a Sales count summery based on areas
CREATE TABLE #Temp
(
[Area] varchar(100)
[SaleCount] int
)
INSERT INTO #Temp
SELECT ‘San Diego’,500
UNION ALL
SELECT ‘Los Angeles’,100
UNION ALL
SELECT ‘Temecula’,10
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT [Area] AS 'td','',[ SaleCount] AS 'td',
FROM #Temp ORDER BY [Area]
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Sales Summary Report</H3>
<table border = 1>
<tr>
<th> Area </th> <th>Sales Count </th> </tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfileName’, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = ‘admin@smartdigitizers.com’, -- replace with your email address
@subject = 'Sales Summary Report – Tabular Format ' ;
DROP TABLE #Temp