June 5, 2015 at 12:19 am
Someone, somewhere, must have done this. Please share if you have.
As many will know, executing
sp_send_dbmail @query='query', [other args here]
sends an e-mail with the results of the query embedded in the body of the e-mail.
The problem is that the results are displayed in a proportional font and they do not line up nicely in columns.
The MS-suggested workaround is to format the results of the query in HTML (see Example C here).
This requires manual coding for every result set you wish to send.
Wouldn't it be nice if you could call
usp_send_dbmail @query='query', @EmailFormat='HTMLTable' (or whatever)
which would then run some fancy SQL to call sp_send_dbmail & send the e-mail in HTML tabular format?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 5, 2015 at 3:39 am
I have a stored procedure that I use for this kind of task.
CREATE PROCEDURE [dba_formatQueryAsHTMLTable]
@query nvarchar(max),
@html nvarchar(max) OUTPUT,
@STYLES nvarchar(max) = NULL
AS
BEGIN
DECLARE @sql nvarchar(max);
DECLARE @header nvarchar(max);
DECLARE @body nvarchar(max);
IF @STYLES IS NULL
SET @STYLES = '
<style>
td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;font-family:calibri;}
th {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;font-family:calibri;font-weight:bold;color:white;background-color:#0080FF;}
</style>'
SET @query = '
SELECT *
INTO #________results
FROM ( ' + @query + ' ) AS src'
SET @sql = '
IF OBJECT_ID(''tempdb..#________results'') IS NOT NULL
DROP TABLE #________results;
' + @query + '
SELECT @header = (
SELECT name AS [text()]
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID(''tempdb..#________results'')
ORDER BY column_id
FOR XML PATH(''TH''), ELEMENTS
)
DECLARE @sql nvarchar(max)
SELECT @sql = STUFF((
SELECT '', '' + QUOTENAME(name) + '' AS
'' AS [text()]
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID(''tempdb..#________results'')
ORDER BY column_id
FOR XML PATH(''''), TYPE
).value(''.'',''nvarchar(max)''),1,1,SPACE(0))
SET @sql =
'' SELECT '' + @sql +
'' FROM #________results ''
SET @sql = '' SELECT @body = ('' + @sql + '' FOR XML RAW(''''TR''''), ELEMENTS XSINIL) ''
EXEC sp_executesql @sql, N''@body nvarchar(max) OUTPUT'', @body OUTPUT
SET @body = REPLACE(@body, '' xsi:nil="true"'','''')
SET @body = REPLACE(@body, '' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'','''')
'
EXEC sys.sp_executesql @sql,
N'@header nvarchar(max) OUTPUT, @body nvarchar(max) OUTPUT',
@header OUTPUT,
@body OUTPUT
SELECT @html = N'
<table cellpadding="0" cellspacing="0" border="0">
' + @STYLES + '
<tr>' + @header + '</tr>' + @body + '
</table>'
END
Usage:
DECLARE @theHTML nvarchar(max);
EXEC dbo.dba_formatQueryAsHTMLTable
@query = 'SELECT * FROM master.dbo.spt_values', -- your query goes here
@html = @theHTML OUTPUT,
@STYLES = NULL -- pass in your own styles or accept the default
Some limitations apply: the query must have column names set and it must be something that can be nested in a FROM clause (see the code to understand what I mean).
Hope this helps
-- Gianluca Sartori
June 5, 2015 at 6:58 am
Oh yes it helps, very nice. Thank you very much!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply