Thursday, 2 March 2017

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.

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' ;

WHAT OTHERS ARE READING/RELATED TO THIS:

0 comments:

Post a Comment

Find job here...