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

STEP1:

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

STEP 2:

/* 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’

CREATE TABLE #Temp (
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
FROM [TLOG_DBMetrics]

set @html1=’<html>
<head>
<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>
</td>
</tr>
</table>
<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>
<tr bgcolor=#CCCCCC>
<td width=”10%” align=”center”>ServerName</td>
<td width=”50%” align=”center”>UserConnection</td>
<td width=”10%” align=”center”>LogDate</td>
</tr>

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

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

if @@ROWCOUNT=0
begin
set @Loopstatus=0
end
else
begin
set @dml1= @DML2+N’<tr><td>’+@td1+N’</td><td>’+@td2+N’</td><td>’+@td3+’</td></tr>’
set @DMl2=@dml1
set @dml1=”

end
set @RowId=@RowId+1

end

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

DROP TABLE #Temp

About these ads

About Prashanth Jayaram

I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in SQL. Bookmark the permalink.

2 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.
    http://sandipgsql.blogspot.in/2013/05/sql-server-send-sql-table-data-via.html

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