email report

I’m writing a report and i want to use SSMS to send it. OK, use ‘msdb.dbo.sp_send_dbmail’ to send it from inside SSMS.

But i want it to be formatted. OK, set @body_format = ‘HTML’ and you can format it with HTML to your little hearts content.

But it looks so ugly. OK, but that is NOT my problem.

Setting up the format for the HTML typically means creating a number of nvarchar(max) local variables, stuffing strings into them and combining the strings into the value of the @body variable. The same stuff you would do with powershell, or C# or whatever. . The typical grunt work of

set @header = '<table border = ''1''><th>Column 1</th><th>...'

You need the double singlequotes to get the single quote around the border (like border = ‘1’)

A couple things will help. One is to find a nicely formatted table, and steal it. It will probably have CSS in it, so be prepared to have something like this:

SET @tableHTML =
 N'<style type="text/css">
 #box-table
 {
 font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
 font-size: 12px;
 text-align: center;
 }
 #box-table th
 {
 font-size: 13px;
 font-weight: normal;
 background: #b9c9fe;
 color: #039;
 }
 #box-table td
 {
 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 [db].[table].[column]</H3>' +
 N'<table id="box-table"> ' + ...

OK, so a standard style sheet at the top, you can fuss with these after it’s working. Next a <H3> Title outside the table, then the top of the table. Start building your columns. You COULD use sys.columns to get a list of all of the table columns, and you could put in a != condition if we want to exclude a column from the report.

Next, we want the data for each column, we want it tagged without having to manually put the </td><td> in a bazillion times, and we want it readable. Sounds like XML to me.

try something like this:

+ CAST ( (
 SELECT td = CAST([ID] AS VARCHAR(100)),'',
 td = [column1_name],'',
 td = [column2_name],'',
 td = [column3_name] ,'',
 td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
 td = CONVERT(VARCHAR(30),[EndDate],120)
 FROM [table]
 ORDER BY [startDate]
 FOR XML RAW('tr'),ELEMENTS)
 ) AS NVARCHAR(MAX) ) +
 N'</table > '

Cast the selections, each aliased to ‘td’ – but that’s ok, because we are selecting FOR XML. Notice you close the table in the last line, or you can have a htmlTail variable as well and glue them together.

Another option would be to have one or more of the td elements something else, like tdc. This will output XML with an invalid HTML element, however you can then do something like this to put special formatting in that/those cell(s).

SET @body = REPLACE(@body, '<tdc>', '<td class="center">')
Advertisements