Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Email Formatted HTML Table with T-SQL Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 9:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:55 AM
Points: 80, Visits: 337
Comments posted to this topic are about the item Email Formatted HTML Table with T-SQL
Post #1465444
Posted Wednesday, June 19, 2013 11:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
I don't believe you'll find anyone more pro-xp_CmdShell or pro_BCP than myself but I can't figure out why you bothered with either for this task since sp_SendDBMail could have easily handled this task. And having to install a 3rd party piece of shareware on a server certainly doesn't make for a "T-SQL Only" project, IMHO.

Why didn't you just use sp_SendDBMail which can take a 2GB VARCHAR(MAX) body with the custom HTML formatting from your article?



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1465464
Posted Thursday, June 20, 2013 12:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:55 AM
Points: 80, Visits: 337
Jeff Moden (6/19/2013)
I don't believe you'll find anyone more pro-xp_CmdShell or pro_BCP than myself but I can't figure out why you bothered with either for this task since sp_SendDBMail could have easily handled this task. And having to install a 3rd party piece of shareware on a server certainly doesn't make for a "T-SQL Only" project, IMHO.

Why didn't you just use sp_SendDBMail which can take a 2GB VARCHAR(MAX) body with the custom HTML formatting from your article?



Thanks for the feedback Jeff!

Basically, I think you are right. I should have written the article with a more modern twist. The practical reason is that I wear many different hats so I end up using Blat for SysAdmin tasks, AD notifications, sending log files, etc. And I was using Blat with SQL Server 7.0. It is just an old habit that works so I haven't changed it.
Post #1465475
Posted Thursday, June 20, 2013 1:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 5, 2013 5:43 AM
Points: 4, Visits: 29
Hey,

This is a Email Formatted HTML table i like to use,..
im sure it would be usefull and easy to use as it is for me

-------------------------------------------------------------------------

-- Email Querry--
DECLARE @Body varchar(max)
declare @TableHead varchar(max)
declare @TableTail varchar(max)
declare @mailitem_id as int
declare @statusMsg as varchar(max)
declare @Error as varchar(max)
declare @Note as varchar(max)

Set NoCount On;
set @mailitem_id = null
set @statusMsg = null
set @Error = null
set @Note = null
Set @TableTail = '</table></body></html>';

--HTML layout--
Set @TableHead = '<html><head>' +
'<H1 style="color: #000000">HEADER OF TABLE</H1>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#F6AC5D>'+
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td></tr>';

--Select information for the Report--
Select @Body= (Select
Column As [TD],
Column As [TD],
Column As [TD],
Column As [TD],
Column As [TD]

FROM [DB].[dbo].[Table]
where -condition-
(whatever you want to do else ...)

For XML raw('tr'), Elements)

-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')


Set @Body = @TableHead + @Body + @TableTail

-- return output--
Select @Body

--Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name =email', <--This is the mail account to sent from.
@mailitem_id = @mailitem_id out,
@recipients='blah@blah.co.za',
@subject = 'subject Email',
@body = @Body,
@body_format = 'HTML';
Post #1465485
Posted Thursday, June 20, 2013 1:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:57 PM
Points: 7, Visits: 211
Why not using CSS?
Example:

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)

SET @subject = 'Query Results in HTML with CSS'


SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green"><th>SpecialOfferID</th>
<th>Description</th>
<th>Type</th>
<th>Category</th>
<th>StartDate</th>
<th>EndDate</th>
</tr>' +
CAST ( (

SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',
td = [Description],'',
td = [Type],'',
td = [Category] ,'',
td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
td = CONVERT(VARCHAR(30),[EndDate],120)
FROM [AdventureWorks].[Sales].[SpecialOffer]
ORDER BY [SpecialOfferID]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'


EXEC msdb.dbo.sp_send_dbmail @recipients='AnyMailYouWant@SqlIsCool.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;


Result:
Post #1465492
Posted Thursday, June 20, 2013 1:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 5, 2013 5:43 AM
Points: 4, Visits: 29
OH Nice !! .. that does look way better...

At the moment my preview only has a colored background for the Clolumn header
As seen in my Attachment (Dont know if you will be able to see it)


  Post Attachments 
email.jpg (95 views, 57.31 KB)
Post #1465496
Posted Thursday, June 20, 2013 2:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:55 AM
Points: 80, Visits: 337
Does that work in Gmail? I recall having an issue putting the <head> tag in the body of the email, so I couldn't use CSS. Although I wanted to.
Post #1465501
Posted Thursday, June 20, 2013 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:57 PM
Points: 7, Visits: 211
No Need to put <head> tag :) this is a In-Line CSS in the <body> of the HTML...
BUT Gmail is striping any Style (CSS) out of the HTML...

i will try to make it work in Gmail... googling....
Post #1465513
Posted Thursday, June 20, 2013 2:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 5, 2013 5:43 AM
Points: 4, Visits: 29

The one i wrote works for gmail as well, its just a straight forward HTML Email format with no fansy addons.
Its a good alternative if you run into compatability problems.



Keep it simple ...
Post #1465519
Posted Thursday, June 20, 2013 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 8, 2014 11:57 PM
Points: 7, Visits: 211
i fixed my code to support the "problematic" gmail which strips any CSS styling...
and as you say "keep it simple" , so NO bcp, NO xp_cmdshell ,NO Blat = ONLY TSQL :)

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @Table NVARCHAR(MAX) = N''

SET @subject = 'Query Results in HTML with CSS'

SELECT @Table = @Table +'<tr style="background-color:'+CASE WHEN (ROW_NUMBER() OVER (ORDER BY [SpecialOfferID]))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END +';">' +
'<td>' + CAST([SpecialOfferID] AS VARCHAR(100))+ '</td>' +
'<td>' + [Description]+ '</td>' +
'<td>' + [Type]+ '</td>' +
'<td>' + [Category] + '</td>' +
'<td>' + CONVERT(VARCHAR(30),[StartDate],120) + '</td>' +
'<td>' + CONVERT(VARCHAR(30),[EndDate],120) + '</td>' +
'</tr>'
FROM [dbo].[SpecialOffer]
ORDER BY [SpecialOfferID]

SET @tableHTML =
N'<H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3>' +
N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:purple;font-family:arial,helvetica,sans-serif;text-align:center;" >' +
N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;">
<th>SpecialOfferID</th>
<th>Description</th>
<th>Type</th>
<th>Category</th>
<th>StartDate</th>
<th>EndDate</th></tr>' + @Table + N'</table>'


EXEC msdb.dbo.sp_send_dbmail @recipients='AnyEmail@SqlIsCool.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;


Result in Gmail:
Post #1465691
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse