HTML formatted table for your SQL alerts or reports
Alerts and reports in plain text are the easiest to setup, fastest to render and smallest to send so why should you bother with HTML formatting? Believe it or not your time and server/network load are not the top concern (or any concern) for most if not all of your users. They want emails showing up looking nice. One look at this example will show I didn’t my degree in design and I have no sense of style, but the structure is there for you to choose your own color pallet to please your users.
I scraped some data from Twitter to load a test table and did a quick sum to see who is the top tweeter in a arbitrary span of time.
If your instance is set up to run msdb.dbo.sp_send_dbmail you should be able to run the script for an example.
CREATE TABLE #testData (rowNumber INT IDENTITY(1,1), person VARCHAR(30), tweets VARCHAR(140))
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','45 minute run in 65℉ wiith 100% humidity. Had to work for that one.')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','Awwww yeah, finally got the domain name I wanted for a running blog.')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','I have a script for that.')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','I say that a lot these days. It''s nice to be able to.')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','If Saturday is for doing nothing useful, I''ve succeeded.')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','I''ll always love string cheese.')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','Ready for lunch! (It''s 9:39 am. Ooops.)')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','Scheduled carpet cleaning. I''m officially done with all adult duties for the day.')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','TO THE CLOUD!')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','To the driving range!')
INSERT INTO #testData (person, tweets) VALUES ('Jes Borland @grrl_geek','When I stand outside I sweat. We have 3 cubic feet of river rock to move. Today is going to suck.')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','Checking out, then off to #sqlcruise')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','Got my feet into the Med. I''m happy. #SQlcruise')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','I see @AaronBertrand and @sqlrnnr #SQLcruise')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','I''m at Plaça de Sant Felip Neri in Barcelona https://www.swarmapp.com/c/7hWHjnWOAt8 ')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','I''m at Xaloc in Barcelona, Catalonia https://www.swarmapp.com/c/h4856sB1K8O ')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','Interesting reading: Haunting chalkboard drawings, frozen in time for 100 years, discovered in Oklahoma school http://buff.ly/1f4R5cs ')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','Interesting reading: It’s The Future http://buff.ly/1Qr1aRI ')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','Interesting reading: The Curious Case of Mencius Moldbug http://buff.ly/1cNmRJn ')
INSERT INTO #testData (person, tweets) VALUES ('Grant Fritchey @Gfritchey','Still in port, but I finished my first presentation. #sqlcruise is off and running.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','My connection is now leaving earlier than originally scheduled.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','I''m so confused. ')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','I wish I had some UA statuse friends who could call and see if I was rebooked yet or not')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Now another 1.5 hour delay #karenstravelstories')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Not sure this math works out for YYZ > ORD > MSP http://ift.tt/1MEZWMH ')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Stayed on tarmac for an hours. Now back to gate to get more fuel. http://ift.tt/1HKjsId ')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Dear @united I sent you a DM love note.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','So we took since tour of the Tarmac, now headed back to gate to get more fuel. #karenstrav… http://ift.tt/1C8SrrH ')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Now a ground stop at ORD. so no take off time now. # karenstravelstories. Connections at risk.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Both of these made me laugh. #dataquality ')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Wow. Now United is serving Prosecco on domestic flights. Better than the Na in breakfast salad.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','The crew doesn''t like being handed a plane full of kicked in the junk passengers.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Oh. So I turns out the crew was still at the gate when the GA made her announcement. They are not happy either')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','I feel like an idiot with a Mac every time I go to touch the IFE on a UA flight. #ItsNotTouch')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','I would have volunteered. But only other option has no F.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Nearly all the pax are saying "oh. It''s our fault?" I think she made a lot of non-cooperative pax here. #karenstravelstories')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','Due to the fact we aren''t getting any volunteers to take another flight, we are going to delay the flight. Really @united? Nasty tone')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','I''m posting odds that he elbows past me at boarding time.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','He also has a white canvas tote, à la LLBean.')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','I ant to ask if he works for JCREW')
INSERT INTO #testData (person, tweets) VALUES ('Karen Lopez @datachick ','I''m judging the guy next to me by how he is dressed. I think he must be a real jerk. ')
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @Table NVARCHAR(MAX) = N''
SET @subject = 'Twitter Status Report' --Subject line of the email
--Load @Table which isn't really a table with your data
SELECT @Table = @Table + '<tr style="background-color:#A3E0FF;">' +
'<td>' + person + '</td>' +
'<td>' + CAST(COUNT(tweets) AS VARCHAR(5)) + '</td>'
FROM #testData
GROUP BY person
ORDER BY COUNT(tweets) DESC
SET @tableHTML =
N'<H3><font color="Red">Who''s the top tweeter?</H3>' + --Title above the table
N'<table border="1" align="left" cellpadding="2" cellspacing="0" style="color:purple; font-family:helvetica; text-align:center;" >' + --Table formatting
N'<tr style ="font-size: 14px; font-weight: normal; background: #b9c9fe; text-align:left;"> ' + --Row level formatting
N'<th>Twitter celebrity</th><th>Number of Tweets</th>' + --Row titles
N'</tr>' + @Table + N'</table>' --Row data
EXEC msdb.dbo.sp_send_dbmail @recipients='dba@importantcompany.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;
DROP TABLE #testData
Tim Peters 