July 18, 2012 at 2:15 am
Hi, I'm trying to have few records appearing with colored background in the email("red" > 80% and "green" between 60% to 80% and "Orange" for < 60%)
The below script gives me an email with few records in a tabular form. I need to have the above condition and get the respective records to be colored. Having difficulty to write the HMTL code.
I want to schedule this script as a job for once a week to my team.
Below is my script which I need to modify to get colors.
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @JobMonitor AS TABLE
(
ServerName VARCHAR(100),
DatabaseId VARCHAR(10),
DatabaseName VARCHAR(50),
Last_Month_DBSize_MB decimal(10,2),
Last_Month_DBSpaceUsed_MB decimal(10,2),
Last_Week_DBSize_MB decimal(10,2),
Last_Week_DBSpaceUsed_MB decimal(10,2),
This_Week_dbsize_MB decimal(10,2),
This_Week_DBSpaceUsed_MB decimal(10,2)
)
INSERT INTO @JobMonitor
with last_month as (
select
databaseid,
DBSize Last_Month_DBSize,
DBSpaceUsed Last_Month_DBSpaceUsed
from
[SQL Instance Name].[msdb].[dbo].[DBSizeTable] nolock
where
cast(floor(CAST(date as float)) as datetime) = CAST(floor(cast(getdate() - 30 as float)) as datetime)
),
last_week as (
select
databaseid,
DBSize Last_Week_DBSize,
DBSpaceUsed Last_Week_DBSpaceUsed
from
[SQL Instance Name].[msdb].[dbo].[DBSizeTable] nolock
where
cast(floor(CAST(date as float)) as datetime) = CAST(floor(cast(getdate()-7 as float)) as datetime)
)
, latest as (
select
databaseid,DatabaseName,
DBSize as This_Week_DBSize,
DBSpaceUsed as This_Week_DBSpaceUsed
from
[SQL Instance Name].[msdb].[dbo].[DBSizeTable] nolock
where
cast(floor(CAST(date as float)) as datetime) = CAST(floor(cast(getdate() as float)) as datetime)
)
select 'SQL Instance Name' as ServerName,
l.DatabaseId,
DatabaseName,
Last_Month_DBSize,
Last_Month_DBSpaceUsed,
'%PercentageUsedLastMonth' = Cast([Last_Month_DBSpaceUsed]/[Last_Month_DBSize] as decimal(10,2))*100,
Last_Week_DBSize,
Last_Week_DBSpaceUsed,
'%PercentageUsedLastWeek' = Cast([Last_Week_DBSpaceUsed]/[Last_Week_DBSize] as decimal(10,2))*100,
l.This_Week_dbsize,
l.This_Week_DBSpaceUsed,
'%PercentageUsedThisWeek' = Cast(l.[This_Week_DBSpaceUsed]/l.[This_Week_dbsize] as decimal(10,2))*100
from
last_week lw inner join
last_month lm on lw.databaseid = lm.databaseid
left join latest l on l.databaseid = lm.databaseid
SET @tableHTML =
N'<H1>XXXXXXXX XXX Instances Database Growth Status</H1>' +
N'<table border="1">' +
N'<tr><th>ServerName</th><th>DatabaseId</th>' +
N'<th>DatabaseName</th><th>Last_Month_DBSize_MB</th><th>Last_Month_DBSpaceUsed_MB</th>' +
N'<th>Last_Week_DBSize_MB</th>' +
N'<th>Last_Week_DBSpaceUsed_MB</th>' +
N'<th>This_Week_dbsize_MB</th>' +
N'<th>This_Week_DBSpaceUsed_MB</th></tr>' +
CAST ( ( SELECT td = ServerName, '',
td = DatabaseId, '',
td = DatabaseName, '',
td = Last_Month_DBSize_MB, '',
td = Last_Month_DBSpaceUsed_MB, '',
td = Last_Week_DBSize_MB,'',
td = Last_Week_DBSpaceUsed_MB,'',
td = This_Week_dbsize_MB,'',
td = This_Week_DBSpaceUsed_MB
FROM @JobMonitor
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
print @tableHTML
EXEC msdb.dbo.sp_send_dbmail @recipients='RECIEPIENT EMAIL ADDRESS',
@subject = 'XXXXXXXX XXX Instances Database Growth Status New',
@body = @tableHTML,
@body_format = 'HTML' ;
July 18, 2012 at 2:19 am
duplicate post
replies here please http://www.sqlservercentral.com/Forums/Topic1331288-392-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply