SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SP Send Query Result as HTML table using DBMail


SP Send Query Result as HTML table using DBMail

Author
Message
david.gugg
david.gugg
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 1017
Comments posted to this topic are about the item SP Send Query Result as HTML table using DBMail


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
qbrt
qbrt
SSC Veteran
SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)SSC Veteran (235 reputation)

Group: General Forum Members
Points: 235 Visits: 638
Hi David,
Thanks for your contribution. I am sure there is someone who may find this sproc very helpful. On the other hand, I shiver whenever I see a sproc with SQL string params with programming logic in it. This type of code, imho, does not belong in a sproc. Having a simple query returning data to a program that in turn wraps it in some UI script would be a better approach. More secure, more efficient.

Don't get me wrong, having working examples like this always helps. I just hope it doesn't find its way to production code.
There should be a better/easier way to send query results through dbmail in HTML format. Until then, we are stuck with this. Crying

sp_send_dbmail: http://msdn.microsoft.com/en-us/library/ms190307.aspx
cody.allen
cody.allen
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 285
I can see this being a fun tool to play with, but seems like a drawn out process of using sp_send_dbmail. By the time I fill in all the variables, I'd just copy my results into a spreadsheet and send it as an attachment.

Also, it seems like on line 43 you are referring to something that isn't built in this sp:

"INSERT INTO #Fields ( field ) SELECT s FROM DBAAdmin.dbo.SplitString(@fieldlist,'|') WHERE zeroBasedOccurance = @j"

I'm assuming you'd have to create a SplitString function first?
david.gugg
david.gugg
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 1017
Sorry, I did forget to include the splitstring function:

CREATE function [dbo].[SplitString] 
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@str,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)



GO



Edit BTW, this was probably not original, but if it isn't I'm not sure of the source.


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
david.gugg
david.gugg
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 1017
Thanks for the replies. In my current system we use this for database consistency checks that we are unable to use hard constraints on. So these emails are only received by the DBA team and development tech leads. It works well for us in that we are able to set nightly jobs with numerous checks and receive any consistency errors in an easy-to-read format.

I can definitely appreciate that there are times and places that other solutions make more sense.


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
Tavis Reddick
Tavis Reddick
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 191
Another way of building this functionality could be to:
1) add a BIT "email result to system admins" parameter to selected stored procedures;
2) put a conditional path in those queries to output FOR XML AUTO, ELEMENTS, ROOT('table') and pass this to your send db email stored procedure;
3) rewrite the send db email stored procedure to take an XML parameter containing the query results from (2);
4) run an XSLT transform http://www.sqlservercentral.com/articles/MDS/75932/ on the output XML to turn it into a HTML table before adding to email body and sending (sample XSLT code below):
DECLARE @xslt XML =
'

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xd="http://www.oxygenxml.com/ns/doc/xsl"
exclude-result-prefixes="xs xd"
version="2.0">
<xd:doc scope="stylesheet">
<xd:desc>
<xd:p><xd:b>Created on:</xd:b> 2014-03-03</xd:p>
<xd:p><xd:b>Author:</xd:b> tavisreddick@fife.ac.uk</xd:p>
<xd:p>Takes a SQL FOR XML AUTO, ELEMENTS, ROOT('table') query result and outputs as HTML table.</xd:p>
</xd:desc>
</xd:doc>
<xsl:output method="xhtml" omit-xml-declaration="yes" />
<xsl:template match="/">
<html>
<head>
<title>HTML table from SQL query</title>
</head>
<body>
<xsl:apply-templates />
</body>
</html>
</xsl:template>
<xsl:template match="table">
<table>
<tr>
<xsl:for-each select="*[last()]/*">
<th><xsl:value-of select="local-name(.)" /></th>
</xsl:for-each>
</tr>
<xsl:for-each select="*">
<tr>
<xsl:for-each select="*">
<td><xsl:value-of select="." /></td>
</xsl:for-each>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>

'



This would be more appealing if you already had XSLT supported in your database, but at least it would keep the send email procedure simpler and more robust, accepting only data and not doing the query itself (and not having to have elevated permissions, maybe).
dplaut 49149
dplaut 49149
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 89
I wish my job was so simple that all I had to do was send the result of a single query but we typically need other text in the email (headers, footers, etc.) and the results of multiple queries.

Nevertheless, nice script.
terry.home
terry.home
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 188
Thanks for sharing the script.

Whilst I appreciate what you are doing and that you have shared your script, I am just wondering if it is not simpler and safer to simply setup SQL Reporting report and then subscribe to it?

T
david.gugg
david.gugg
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1448 Visits: 1017
terry.home (3/28/2015)
Thanks for sharing the script.

Whilst I appreciate what you are doing and that you have shared your script, I am just wondering if it is not simpler and safer to simply setup SQL Reporting report and then subscribe to it?

T


For our purposes, we have maybe a hundred little checks set up to maintain consistency of data across multiple third party software platforms. Could we set each up in SSRS? Yes, definitely. However since this is internal reporting, usually just to the DBA team, it is easier to use the shown stored procedure.


Personal blog relating fishing to database administration:

https://davegugg.wordpress.com/
dplaut 49149
dplaut 49149
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 89
I agree, reporting services would be overkill for most DBA stuff. I often want an email sent as part of a SQL server agent job and this is perfect for that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search