BERJAYA

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= @[email protected];[email protected]

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=’[email protected];[email protected]’

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,–‘[email protected];[email protected]’,
@subject = @subject,
@body = @html2,
@body_format = ‘HTML’;

DROP TABLE #Temp

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL [email protected] The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/

View all posts by Prashanth Jayaram →