Email Formatted HTML Table with T-SQL

Question

Email Formatted HTML Table with T-SQL

February 16, 2017 at 5:12 am

This was removed by the editor as SPAM

Viewing 8 posts - 76 through 82 (of 82 total)

You must be logged in to reply to this topic. Login to reply

tgqthi SSC Journeyman Points: 79 More actions · Answer 1

Jeff Moden (3/15/2016)
tauseef.jan (3/15/2016)
Thankyou so much for the code snippet.
But I have a problem, that I have EMPTY(NULL) columns.
because of this the columns in the email get shifted to the left.
Please help to resolve this
Most likely, you'll need to replace the "empty" cells with non-breaking spaces (I forget the HTML entitization code for that but should be easy for you to find).

use ISNULL

Example:

SET @BODY = ISNULL(@Sintetico,'') + @QuebraN + ISNULL(@ExplodeRJ,'')

EXEC msdb.dbo.sp_send_dbmail

@recipients='ti@thermoklima.com.br',

@subject = '',

@BODY = @BODY,

@body_format = 'HTML'

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

tgqthi (8/30/2016)
Jeff Moden (3/15/2016)
tauseef.jan (3/15/2016)
Thankyou so much for the code snippet.
But I have a problem, that I have EMPTY(NULL) columns.
because of this the columns in the email get shifted to the left.
Please help to resolve this
Most likely, you'll need to replace the "empty" cells with non-breaking spaces (I forget the HTML entitization code for that but should be easy for you to find).
use ISNULL
Example:
SET @BODY = ISNULL(@Sintetico,'') + @QuebraN + ISNULL(@ExplodeRJ,'')
EXEC msdb.dbo.sp_send_dbmail
@recipients='ti@thermoklima.com.br',
@subject = '',
@BODY = @BODY,
@body_format = 'HTML'

Pretty sure that won't actually help with empty "Cells" because HTML basically ignores leading/trailing spaces and cells that contain nothing but spaces.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

welcome.to.route66 SSC Veteran Points: 249 More actions · Answer 3

take a look at vsql-email (sql-email.com) this tool is exactly what I needed

DamianC SSCrazy Eights Points: 8015 More actions · Answer 4

I tend to drop whatever I need to audit from the ETL process into a table, the use a BI tool (in my case, SSRS or Cognos) to produce and send a report

That said, I was aware but had never really played around with e-mailing with formatting

Just used the ideas presented here (initial plus some of the discussed alternatives) to produce a useful report that runs part way through my ETL build

Thanks

- Damian

thakur.bhagat24 SSC Rookie Points: 45 More actions · Answer 5

In previous example is very useful

But I have one new requirement of order by color what we get using below case.

CASE WHEN (ROW_NUMBER() OVER (ORDER BY [SpecialOfferID]))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END

i.e order by '#A3E0FF' OR

i.e Order by '#8ED1FB'

Lowell SSC Guru Points: 323504 More actions · Answer 6

thakur.bhagat24 (12/20/2016)
In previous example is very useful
But I have one new requirement of order by color what we get using below case.
CASE WHEN (ROW_NUMBER() OVER (ORDER BY [SpecialOfferID]))%2 =1 THEN '#A3E0FF' ELSE '#8ED1FB' END
i.e order by '#A3E0FF' OR
i.e Order by '#8ED1FB'

you can use Cascading Style Sheets and the nth-child to set the row color of every other row automatically:

the

<style TYPE="text/css">

tr:nth-child(odd){ background-color:#eee; }

tr:nth-child(even){ background-color:#fff; }

</STYLE>

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

cunay Grasshopper Points: 22 More actions · Answer 7

nakache - Thursday, June 20, 2013 1:35 AM
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:

nakache, thanks for your help, I did not know SQL supports for CSS. I was editing all the HMTL tags from the beginning for each code. This saved my time bro.