February 24, 2014 at 8:33 pm
Comments posted to this topic are about the item SP Send Query Result as HTML table using DBMail
Be still, and know that I am God - Psalm 46:10
February 25, 2014 at 6:09 am
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
February 25, 2014 at 7:10 am
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-2"
I'm assuming you'd have to create a SplitString function first?
February 25, 2014 at 7:29 am
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.
Be still, and know that I am God - Psalm 46:10
February 25, 2014 at 7:35 am
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.
Be still, and know that I am God - Psalm 46:10
March 3, 2014 at 5:40 am
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).
March 26, 2015 at 6:05 am
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.
March 28, 2015 at 2:14 pm
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
March 30, 2015 at 7:09 am
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.
Be still, and know that I am God - Psalm 46:10
March 31, 2015 at 6:31 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply