So you have set up a whole load of warnings via email, However they are starting to look messy now. Its hard to make out what they are saying.
How about checking them into a nice color coded HTML table. And make it look all lovely and pretty.
Check this code out:-
DECLARE @EmailAddress varchar(1000)
select @EmailAddress = 'Mail@mail.com'
IF (SELECT COUNT(*) FROM <Table>
where [Feild1] <> '') > 0
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @Mailsubject nvarchar(255)
SET @Mailsubject = 'DBA - Warning its all dead Dave'
SET @tableHTML =
N'<html><head><style type="text/css">'+
N'body'+
N'{'+
N'font: 12px arial'+
N'}'+
N'</style></head>'+
N'<body>'+
N'<H3>Dave Its Dead, Its Dead Dave</H3>' +
N'<table border="1">' +
N'<th>Feild1</th>' +
N'<th>Feild2</th>' +
N'<th>Feild3</th>' +
N'</tr>' +
ISNULL(CAST ( (
select
case
when [Feild1] like '%somthing%'
then
'#F78181'
when [feild1] like '%somthingelse%'
then
'#FF0000'
when [feild1] like '%Anotherthing%'
then
'#00FFFF'
else '#FFFFFF'
end AS [@bgcolor],
td = feild3, ''
, td = feild2, ''
, td = [feild1]
from Table
where [feild1] <> ''
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ),'') +
N'</table></body></html>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email_Profile',
@recipients= @EmailAddress,
@subject = @Mailsubject,
@body = @tableHTML,
@body_format = 'HTML' ;
END
How about checking them into a nice color coded HTML table. And make it look all lovely and pretty.
Check this code out:-
DECLARE @EmailAddress varchar(1000)
select @EmailAddress = 'Mail@mail.com'
IF (SELECT COUNT(*) FROM <Table>
where [Feild1] <> '') > 0
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @Mailsubject nvarchar(255)
SET @Mailsubject = 'DBA - Warning its all dead Dave'
SET @tableHTML =
N'<html><head><style type="text/css">'+
N'body'+
N'{'+
N'font: 12px arial'+
N'}'+
N'</style></head>'+
N'<body>'+
N'<H3>Dave Its Dead, Its Dead Dave</H3>' +
N'<table border="1">' +
N'<th>Feild1</th>' +
N'<th>Feild2</th>' +
N'<th>Feild3</th>' +
N'</tr>' +
ISNULL(CAST ( (
select
case
when [Feild1] like '%somthing%'
then
'#F78181'
when [feild1] like '%somthingelse%'
then
'#FF0000'
when [feild1] like '%Anotherthing%'
then
'#00FFFF'
else '#FFFFFF'
end AS [@bgcolor],
td = feild3, ''
, td = feild2, ''
, td = [feild1]
from Table
where [feild1] <> ''
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ),'') +
N'</table></body></html>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email_Profile',
@recipients= @EmailAddress,
@subject = @Mailsubject,
@body = @tableHTML,
@body_format = 'HTML' ;
END
No comments:
Post a Comment
Your views:-