Alternative and Best ways to format SQL query for

Question

I'm creating an email alert that will most likely be routed to smart phones (either email or SMS) and be used to wake people up when we have production issues. Email is send with EXEC msdb.dbo.sp_send_dbmail and providing the @query parameter which executes a stored proc.

Currently, the report looks something like this, and as you can see, when things wrap around and are not in a Courier font, they look horrible.

What is the best way to format this? HTML emails instead with a table? Are there any T-SQL utilities to do that? Is this perhaps a job for C# instead of T-SQL (I can code either).

> Rating     Airline    Feed             LocalDateTimeLastMsgRcvd
> AlertMinutes CountMessageInLastHour AvgCountPerSameHour
> ---------- ---------- ---------------- ------------------------ ------------ ---------------------- ------------------- GOOD       ABC        Feed1             2012-08-22 07:15                   10               
> 420                 351 BAD        ABC        ABC-Feed2        
> 2012-08-22 07:04                   10                     48          
> 56 GOOD       ABC        ABC-Feed3         2012-08-22 06:40           
> 75                      1                   1 GOOD       DEF       
> DEF               2012-08-22 07:15                   10               
> 597                 858

I am considering three ideas:

1) Use T-SQL to write each element on different line of temp table:

Row=1
Rating=Good
Feed=ABC 
LocalDateTimeLastMsgRcvd=2012-08-22 07:04 
etc..

.

2) Similar to above but use XML wrappers

3) Similar to above but use <TR><TD> wrappers to make HTML table.

I'm currently looking at option c here: http://msdn.microsoft.com/en-us/library/ms190307.aspx, which will probably work for most smartphone emails, but probably not SMS. Some people will forward this to their SMS.

Answer 1

Formatting is not something stored procedures or SQL is known for. It might be worth considering XML output and, possibly, XSLT.

Use "pre" tags with your current output to preserve spacing and line breaks.

I've seen SMS messages take 24 hours to deliver. It doesn't happen to me often, but it does happen regularly. (Maybe once a year, and I don't text as much as most people.)

• Send only the critical information, and
• make it less than 160 characters.

On a phone, I think I'd rather read something like your #1, but I guess that might depend on how much information is critical. Wide tables--and yours strikes me as wide--are hard for me to read on the phone.

Whether it makes sense to rely on people forwarding certain emails to their SMS is really application-dependent. You're the only one here who can tell whether that might be reliable.