Email Formatted HTML Table with T-SQL

Question

Email Formatted HTML Table with T-SQL

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

D-SQL SSCrazy Points: 2265 More actions · Answer 1

This is great I have something similar. What if in your type you wanted to make the cell red if it was a certain criteria is there a way to do that?

greg.bull SSCrazy Points: 2093 More actions · Answer 2

Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?

In essence to combine these:

'font/@color' = case...(set up to return color # here), td = colname, ''

'td/@bgcolor' = case...(set up to return color # here), td = colname, ''

I've tried a few variations, but with not much success.

Regards, Greg.

deshmukhp12384 SSC Enthusiast Points: 132 More actions · Answer 3

i m trying this code after providing profilename:the mail is gone in queued status.mail can not send

deshmukhp12384 SSC Enthusiast Points: 132 More actions · Answer 4

DECLARE @bodyMsg nvarchar(max)

DECLARE @subject nvarchar(max)

DECLARE @tableHTML nvarchar(max)

SET @subject = 'XBRL Report'

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>Version</th>

<th>Total</th>

<th>Processed</th>

<th>UnProcessed</th>

</tr>' +

CAST ( (

SELECT td = CAST([Requested_By] AS VARCHAR(100)),'',

td = [Requested_By],'',

td =[Total_Count],'',

td = [UnProcessed_Count] ,'',

td = [Processed_Count]

FROM@TEMP_XBRL_DETAILS

FORXML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>'

EXEC msdb.dbo.sp_send_dbmail @recipients='deshmukhp12384@gmail.com',

@subject = @subject,

@body = @tableHTML,

@body_format = 'HTML',

@profile_name ='XBRL_Admin ';

print @tableHTML;

this gives me result:

(1 row(s) affected)

Mail (Id: 15) queued.

#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><H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3><table id="box-table" ><tr><font color="Green"><th>Version</th>

<th>Total</th>

<th>Processed</th>

<th>UnProcessed</th>

</tr><tr><td>Web</td><td>Web</td><td>432</td><td>0</td><td>432</td></tr></table>

Kindly help me do done it sucessfully

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 5

greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?
In essence to combine these:
'font/@color' = case...(set up to return color # here), td = colname, ''
'td/@bgcolor' = case...(set up to return color # here), td = colname, ''
I've tried a few variations, but with not much success.
Regards, Greg.

I know this post is a bit old but here's a section of the code that I use to create one of my morning reports that does these types of things. Using CSS would probably be the better way but I didn't know how to even spell CSS when I first did this.

--===== Create the starting tags, table definition, title caption for the table, and column headers.

SELECT @Body =

'

<html>

<Body>

<table border="1" cellpadding = "4" cellspacing="0" style="font-family:arial; color:black; font-size:10pt;">

<caption style=" border:1; background-color:White; font-weight:bold; font-size:14pt;">

Morning Report - 24 Hour Job Summary for ' + @@SERVERNAME + '

~ ' + CONVERT(CHAR(17),GETDATE(),113) + ' ~

</caption>

<tr style="background-color:Blue; color:White; ">

<th>Job Name</th>

<th>Is Enabled</th>

<th>Is Scheduled</th>

<th>Next Run Date</th>

<th>Min Run Date</th>

<th>Max Run Date</th>

<th>Max Duration</th>

<th>Total Runs</th>

<th>Passed</th>

<th>Failed</th>

<th>Retried</th>

<th>Cancelled</th>

<th>Last Modified</th>

</tr>

--'

+ REPLACE(REPLACE(CAST(

( --=== This formats each column as HTML. CASE statements provide changes in background color.

SELECT

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

= 'left', td = JobName,'',

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

= CASE WHEN IsEnabled = 'Yes' THEN 'background-color:White;' ELSE 'background-color:Yellow;' END,

= 'center', td = IsEnabled,'',

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

= CASE

WHEN IsScheduled ='Yes' THEN 'background-color:White;'

WHEN IsScheduled ='One Time' THEN 'background-color:DarkOrange;'

ELSE 'background-color:Yellow;'

END,

= 'center', td = IsScheduled,'',

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

= CASE

WHEN NextRunDate = '-' THEN 'background-color:White;' -- Rev 03

WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-30,GETDATE())

THEN 'background-color:Red;'

WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-7,GETDATE())

THEN 'background-color:Yellow;'

ELSE 'background-color:White;'

END,

= 'center', td = NextRunDate,'',

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

= 'center', td = MinRunDate,'',

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

= 'center', td = MaxRunDate,'',

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

= 'center', td = MaxDuration,'',

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

= CASE

WHEN IsScheduled = 'Yes' AND TotalRuns = '0'

THEN 'background-color:Yellow;'

ELSE 'background-color:White;'

END,

= 'right', td = TotalRuns,'',

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

= CASE WHEN Passed = TotalRuns THEN 'background-color:White;' ELSE 'background-color:Red;' END,

= 'right', td = Passed,'',

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

= CASE WHEN Failed IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,

= 'right', td = Failed,'',

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

= CASE WHEN Retried IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,

= 'right', td = Retried,'',

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

= CASE WHEN Cancelled IN ('0','-')THEN 'background-color:White;' ELSE 'background-color:Red;' END,

= 'right', td = Cancelled,'',

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

= 'center', td = LastModified,''

FROM cteAssembleReport

ORDER BY JobName

FOR XML PATH('tr'), TYPE --This concatenates everything nicely in <tr></tr> tags for each row in the final table.

)

--===== This formats the HTML for human readability for troubleshooting purposes. (End of CAST and REPLACE's)

AS VARCHAR(MAX)), '</tr>','</tr>'+CHAR(13)+CHAR(10)), '<tr>',SPACE(12)+'<tr>')

+ --=== This finalizes the starting tags.

' </table>

</body>

</html>

'

;

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

Aleksandr Furman Ten Centuries Points: 1261 More actions · Answer 6

I usually first create an HTML document using some WYSIWYG (what you see is what you get) tool apply all the formatting I want through the interface and then look at the underlying HTML. Any version of Visual Studio works, you can even save Word document as .htm and see it in any text editor. That's how I've learned html in the first place.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Jeff Moden (8/25/2015)
greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?
In essence to combine these:
'font/@color' = case...(set up to return color # here), td = colname, ''
'td/@bgcolor' = case...(set up to return color # here), td = colname, ''
I've tried a few variations, but with not much success.
Regards, Greg.
I know this post is a bit old but here's a section of the code that I use to create one of my morning reports that does these types of things. Using CSS would probably be the better way but I didn't know how to even spell CSS when I first did this.

Actually Jeff, for html email, inline styles work best across most email clients, so your lack of spelling ability will have been a benefit this time

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 8

mister.magoo (8/25/2015)
Jeff Moden (8/25/2015)
greg.bull (4/27/2015)
Just wondering if anyone has worked out how to control foreground AND background colours for cells in a formatted table?
In essence to combine these:
'font/@color' = case...(set up to return color # here), td = colname, ''
'td/@bgcolor' = case...(set up to return color # here), td = colname, ''
I've tried a few variations, but with not much success.
Regards, Greg.
I know this post is a bit old but here's a section of the code that I use to create one of my morning reports that does these types of things. Using CSS would probably be the better way but I didn't know how to even spell CSS when I first did this.
Actually Jeff, for html email, inline styles work best across most email clients, so your lack of spelling ability will have been a benefit this time

Excellent. That's good to know because when it comes to CSS, I have a major case of CRS.

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

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Interesting how many different ways there are to do this.

Back in the early SQL 2000 days I wanted to send HTML-formatted reports from SQL Server; DB_SENDMAIL was the first option I considered, and was very quickly rejected, too much hassle with profiles (our senior SysOp had some very picturesque things to say about having windows-recognisable email profiles on Windows 2000 Server systems that were expected to be reliable). At one point we had xp_commandshell calling cscript to execute a JS script to send email, but that was superseded by a queuing approach, because in the early days (before SQL 2000 SP2 if I remember correctly) XP_CMDSHELL was kind of insecure (I think the proxy mechanism was broken).

There were two sorts of report to be sent: things that were initiated from within SQL part of the system (problem reports) and things that were requested from outside (status reports, summaries, and so on). The SQL initiated things were all pretty short, and could be dumped into queue (a table) that would be serviced by an aynchronous task (scheduled by SQL Agent) with columns for to, cc, bcc, from, subject, priority and body fields; and the things initiated from ouside could mostly be made to be jobs run by SQL Agent (either scheduled for regular reports and summaries or by manually starting the job - using a privileged sp that ony started that job- for on demand things). Generally the jobs were ACTIVESCRIPTING jobsteps (often called ACTIVE-X jobsteps or JavaScript jobsteps) that called a stored procedure to get the formatted data and mailing parameters (and in the case of the queue servicer the priority) and then created a an email object using them and called its send method. so no profiles required. The synchronous stuff (but not the stuff passed through the queue) could potentially have longer bodies because multiple rows could be returned by an SP, but I don't think that was ever exploited. If some sql detected a problem that warranted instant email it could park the message in the queue and start the job to service the queue (if it wasn't already running because of its schedule).

Tom

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 10

The very first comment I received after posting this article was from Jeff Moden ("RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row") saying basically, "Why don't you just use Database Mail?" Per usual, Jeff is right. There are three steps to the article, but it should really be just two - create the proc for the HTML and then a job that executes the proc. Below is how to do that. I will add an edit to the article.

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'MYSERVER Email',

@recipients = 'foo@foo.com',

@subject = 'The Email Subject',

@query =N'EXECUTE MYDB.dbo.procHtmlSql',

@attach_query_result_as_file = 0,

@body_format = 'HTML';

Steve

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 11

TomThomson (8/25/2015)
Interesting how many different ways there are to do this.
Back in the early SQL 2000 days I wanted to send HTML-formatted reports from SQL Server; DB_SENDMAIL was the first option I considered, and was very quickly rejected, too much hassle with profiles (our senior SysOp had some very picturesque things to say about having windows-recognisable email profiles on Windows 2000 Server systems that were expected to be reliable). At one point we had xp_commandshell calling cscript to execute a JS script to send email, but that was superseded by a queuing approach, because in the early days (before SQL 2000 SP2 if I remember correctly) XP_CMDSHELL was kind of insecure (I think the proxy mechanism was broken).

I have email setup on all the servers that need it but, to be honest, I don't usually use it for any of the DBA reports that I generate nor any of the emails that I may have to send due to the ad hoc request. Although it has changed over time and allows a bit more flexibility than it used to (especially in the area of the FROM address), old habits are hard to break (because they do work well in this case) and I still use CDOSYS routines (saved in my UTIL database, of course) to send emails. It does such nice things as automatically filling in the current server name @NoReply if there's no FROM address and filling in the Subject line with another server oriented message if there's no Subject.

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

Michael Meierruth SSChampion Points: 10051 More actions · Answer 12

Has anyone ever done the reverse operation? I.e. a table from a web page saved to an html file loaded into a SQL Server table.

Lowell SSC Guru Points: 323522 More actions · Answer 13

Michael Meierruth (8/28/2015)
Has anyone ever done the reverse operation? I.e. a table from a web page saved to an html file loaded into a SQL Server table.

I user a CLR to to call web services or download the html of a web page, so that it can be parsed or scraped for the data in the page itself.

so the CLR is the easy part, it's parsing the varchar(max) results to extract the relevant pieces you want that costs some time. every page is kind of different; you could iterate through each table/tr/td pretty easily, but a lot of sites are switching over to nested divs, that look the same as a table, but you have to parse them differently.

//requires

//Imports System.Net

[Microsoft.SqlServer.Server.SqlFunction()]

public static SqlChars CLR_WebQuery(string URL)

{

// SqlString = varchar(8000) , SqlChars=varchar(max)

WebRequest request = HttpWebRequest.Create(URL);

using (WebResponse response = request.GetResponse()) {

using (Stream dataStream = response.GetResponseStream()) {

using (StreamReader reader = new StreamReader(dataStream)) {

string responseFromServer = reader.ReadToEnd();

return new SqlChars(new SqlString(responseFromServer));

}

request = null;

}

VB.Net Version:

'requires

'Imports System.Net

<Microsoft.SqlServer.Server.SqlFunction()>

Public Shared Function CLR_WebQuery(ByVal URL As String) As SqlChars ' SqlString = varchar(8000) , SqlChars=varchar(max)

Dim request As WebRequest = HttpWebRequest.Create(URL)

Using response As WebResponse = request.GetResponse()

Using dataStream As Stream = response.GetResponseStream()

Using reader As New StreamReader(dataStream)

Dim responseFromServer As String = reader.ReadToEnd()

Return New SqlChars(New SqlString(responseFromServer))

End Using

request = Nothing

End Function

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!

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 14

I haven't but this is a cool browser plug-in that you might be able to use: https://www.kimonolabs.com

It wouldn't be saving the html file but in theory you can access the data in the web page table via the API then insert the data in MSSQL. Probably would need some sort of .NET app to do it though, or CLR.

Wanderlei Santos SSC Veteran Points: 221 More actions · Answer 15

One way we use something like this is to send alerts. A scheduled job checks for problems, and emails a formatted html report to a list of recipients with sp_send_dbmail. We then text the recipients, just saying 'please check your email', by emailing to something like 1231231234@txt.att.net. No need for third-party messaging tools/subscriptions.

Email Formatted HTML Table with T-SQL

Cookies on SQLServerCentral