SQL – Table data – HTML Format & Email Sending using T-SQL

1) Configure DBMail  – In this case the ‘TEST’ profile being created for the demo

2) Create Table – TLOG_Metrics
3) Insert the dumnmy values or You can schedule a job to store the data in TLOG_Metrics table
4) Execute the script


CREATE TABLE [dbo].[TLOG_DBMetrics](
[ServerName] [varchar](1000) NULL,
[userconnections] [varchar](1000) NULL,
[LogDate] [datetime] NOT NULL


/* Insert DUMMY Values*/

INSERT INTO TLOG_METRICS VALUES(‘Server01′,’247.00′,’2013-04-22 11:41:21.290’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’254.00′,’2013-04-22 11:31:23.390’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’259.00′,’2013-04-22 11:21:21.713’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’245.00′,’2013-04-22 11:11:21.370’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’295.00′,’2013-04-22 11:01:29.170’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’246.00′,’2013-04-22 10:51:20.510’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’239.00′,’2013-04-22 10:41:23.800’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’251.00′,’2013-04-22 10:32:35.633’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’247.00′,’2013-04-22 10:21:20.907’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’245.00′,’2013-04-22 10:11:26.327’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’289.00′,’2013-04-22 10:01:22.460’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’240.00′,’2013-04-21 09:51:21.190’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’236.00′,’2013-04-21 09:41:21.787’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’246.00′,’2013-04-22 09:31:23.463’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’238.00′,’2013-04-22 09:21:22.093’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’254.00′,’2013-04-22 09:11:22.360’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’282.00′,’2013-04-22 09:01:25.113′)

STEP 3: /* Replace @ProfileName and Recipients list in the below script*/ For Example – SET @ProfileName =’PowerSQL’ and SET @recipients= @abc@mail.com;def@mail.com

Execute the below script

DECLARE @html1 nvarchar(MAX),
@html2 nvarchar(MAX),
@dml1 nvarchar(MAX),
@dml2 nvarchar(MAX),
@td1 varchar(10),
@td2 varchar(10),
@td3 varchar(10),
@td4 varchar(10),
@subject varchar(100),
@RowId1 int,
@LoopStatus1 int,
@RowId int,
@Loopstatus int,
@ProfileName nvarchar(50),
@recipients nvarchar(100)

SET @ProfileName=’TEST’
SET @recipients=’ABC@MAIL.COM;def@mail.com’

ID INT identity(1,1),
[ServerName] [varchar](1000) NULL,
[userconnections] [varchar](1000) NULL,
[LogDate] [datetime] NOT NULL)

INSERT INTO #Temp(ServerName,userconnections,LogDate)
SELECT top 10 ServerName,userconnections,LogDate

set @html1='<html>
<STYLE TYPE=text/css>
<table width=”100%” border=1>
<tr bgcolor=”#CCCCCC”>
<td colspan=”7” height=”25” align=”center”>
<font face=”tahoma” color=”#003399” size=”4”>
<strong>User Activity Report</strong></font>
<table width=”100%” border=1 ><tbody>
<tr bgcolor=”#CCCCCC”>
<td width=”100%” align=”center” colSpan=3><font face=”tahoma” color=”#003399” size=”2”>
<strong> No of User Active connections</strong></font></td>
<tr bgcolor=#CCCCCC>
<td width=”10%” align=”center”>ServerName</td>
<td width=”50%” align=”center”>UserConnection</td>
<td width=”10%” align=”center”>LogDate</td>

set @loopstatus=1
set @RowId=1
set @dml2=”
set @dml1=”

While @Loopstatus<>0
@td1 = servername,
@td2 = userconnections,
@td3 = convert(varchar(10),Logdate,110)
from #Temp where id=@RowId

set @Loopstatus=0
set @dml1= @DML2+N'<tr><td>’+@td1+N'</td><td>’+@td2+N'</td><td>’+@td3+'</td></tr>’
set @DMl2=@dml1
set @dml1=”

set @RowId=@RowId+1


SET @html2=@html1+@Dml2+'</table>’

print @html2

set @subject=’ User Activity Report ‘+ CONVERT(varchar(10),getdate(),110)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients= @recipients,–‘abc@mail.com;def@mail.com’,
@subject = @subject,
@body = @html2,
@body_format = ‘HTML’;



About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL. Bookmark the permalink.

3 Responses to SQL – Table data – HTML Format & Email Sending using T-SQL

  1. aditi Sandip Patil says:

    Check below link for send table data by email in html format.

  2. Allena Smtih says:

    Thanks really beneficial. Will share site with my friends.|

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s