September 25, 2008 at 2:19 am
I'm sending a notification email in HTML format and there is data in a table function type i want to send in the email as a html table. is there a neat way to do it or do i need to get messy looping though the table value?
the IDEAL solution would have been if reporting services subscription model wasn't so primative and could send reports in a conditional manner. if i could suck in a report and send it via email from an sql server job (like below) that'd be great as well - unfortunately that's way way beyond my skill level.
DECLARE @results as table(target_name VARCHAR(50), target_value numeric);
INSERT INTO @results (target_name , target_value )
(SELECT target_name , target_value from foo);
IF
(SELECT count(*) from @results) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients='me@foobar.com',
@subject = 'Approching targets',
@body = @results, <-- convert that guy into a html table
@body_format = 'HTML'
END;
September 25, 2008 at 4:56 am
This code dispenses with the need to create a table variable; results are drawn directly from your table "foo" using the FOR XML clause to do the tagging.
Whilst this works OK, I suspect there's a smarter way of constructing the HTML table; I couldn't work out how else to generate the table header and detail elements in the same query.
DECLARE @xml1 as nvarchar(max)
DECLARE @mailbody as nvarchar(max)
-- Set up table and th tags
SET @xml1 =
(SELECT Tag
,Parent
,[table!1!border]
,[tr!2!th!ELEMENT]
,[tr!2!tn!ELEMENT] AS [tr!2!th!ELEMENT]
FROM
(SELECT 1 AS Tag
,NULL AS PARENT
,1 AS [table!1!border]
,NULL AS [tr!2!th!ELEMENT]
,NULL AS [tr!2!tn!ELEMENT]
UNION ALL
SELECT 2 AS Tag
,1 AS Parent
,NULL AS [table!1!border]
,'name' AS [tr!2!td!ELEMENT]
,'value' AS [tr!2!tn!ELEMENT]
) AS a
for XML EXPLICIT)
-- Set up td tags
SET @mailbody =
(SELECT 1 AS Tag
,NULL AS Parent
,target_name AS [tr!1!td!ELEMENT]
,target_value AS [tr!1!td!ELEMENT]
FROM foo
FOR XML EXPLICIT)
-- Concatenate header and detail together
-- SET @mailbody = replace(@xml1,' ')
The SQLServerCentral comment filter prevents the line above from being displayed correctly; I've attached a text file with the complete code
-- @mailbody can now be used in mail, or whatever
SELECT @mailbody
September 25, 2008 at 4:03 pm
hmm yeah i really wanted to stay away from storing any formatting in the sql code, doesn't gel with our projects layout of data->business rules->formatting
maybe i've just uncovered a new feature request
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply