How Generate Email Formatted HTML Table with T-SQL
If you are database developer this is very common task to send automated email, and for that you need to created good html formatted table using your SQL Server table data. Here we are going to do same.
First Create a temp table to insert sample table. After that use below body code in which we have already implemented css. after that you will html table you can see in above image.
First Create a temp table to insert sample table. After that use below body code in which we have already implemented css. after that you will html table you can see in above image.
CREATE TABLE #temp( JobName NVARCHAR(100),StepName NVARCHAR(100), ErrorMsg NVARCHAR(200))
INSERT INTO #temp VALUES('Job 1','Step 1','Error no 1'),('Job 1','Step 2','Error no 2')
SELECT * FROM #temp
DECLARE @Xml NVARCHAR(MAX)
DECLARE @Body NVARCHAR(MAX)
SET @Xml = CAST(( SELECT [JobName] AS 'td','',[StepName] AS 'td','',[ErrorMsg] AS 'td'FROM #Temp
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Body =N'<html>
<head><style
type="text/css">th { background-color: #4CAF50; color: white;
border: solid black 1px; padding: 10px 10px 10px 10px;font-size:12pt;}
td {border: solid black 1px;padding: 5px
5px 5px 5px;font-size:11pt;color:Black;}
table {border-collapse: collapse; }
body {font-family: "Arial",
Helvetica, sans-serif;}</style></head>
<body><H3>Failed Job
Detail</H3>
<table> <tr><th>Job
Name</th> <th>Step Name</th>
<th>ErrorMessage</th></tr>'
SET @Body = @Body + @Xml +'</table></body></html>'
PRINT @Body
EXEC msdb.dbo.sp_send_dbmail
@Profile_name =
'SQL ALERTING', -- replace with your SQL Database Mail Profile
@Body = @Body,
@Body_format ='HTML',
@Recipients =
'ahlawatvikas.gmail.com',
-- replace with your email address
@Subject = 'E-mail in Tabular Format' ;
Comments