设置电话警报消息的SQL查询格式的替代方法和最佳方法 [英] Alternative and Best ways to format SQL query for phone alert messages
问题描述
我正在创建一个电子邮件警报，该警报很可能会路由到智能手机（电子邮件或SMS），并在出现生产问题时用于唤醒人们。电子邮件使用EXEC msdb.dbo.sp_send_dbmail发送，并提供执行存储过程的@query参数。
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.
当前，该报告看起来像这样，正如您所看到的，当事情回绕而不是使用Courier字体时，它们看起来很可怕。
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.
格式化此格式的最佳方法是什么？ HTML电子邮件而不是表格？
是否有任何T-SQL实用程序可以做到这一点？这也许是C＃而不是T-SQL的工作（我也可以编码）。
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
我正在考虑三个想法：
1）使用T-SQL在临时表的不同行上写入每个元素：
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）与上述类似，但使用XML包装器
2) Similar to above but use XML wrappers
3）与上述类似，但使用< TR>< TD> 包装器来制作HTML表。
3) Similar to above but use <TR><TD> wrappers to make HTML table.
我目前在这里查看选项c： http://msdn.microsoft.com/en-us/library/ms190307.aspx ，它可能适用于大多数智能手机电子邮件，但可能不适用于SMS。有些人会将其转发到他们的SMS。
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.
推荐答案
格式化不是存储过程或SQL已知的东西。可能值得考虑XML输出以及XSLT。
Formatting is not something stored procedures or SQL is known for. It might be worth considering XML output and, possibly, XSLT.
在当前输出中使用 pre标签以保留间距和换行符。
Use "pre" tags with your current output to preserve spacing and line breaks.
我已经看到SMS消息需要24小时才能完成交付。它不是经常发生在我身上，但它确实会定期发生。 （也许每年一次，但我发短信的次数不如大多数人。）
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.)
- 仅发送关键信息，并且
- 使其少于160个字符。
在电话上，我想我更愿意阅读类似于您的＃1，但我想这可能取决于关键信息的数量。桌子很宽，而您的桌子却让我大吃一惊，这让我很难在电话上阅读。
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.
依靠人们将某些电子邮件转发给他们的邮件是否有意义SMS确实取决于应用程序。您是唯一可以告诉我们是否可靠的人。
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.
这篇关于设置电话警报消息的SQL查询格式的替代方法和最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
