using sp_send_dbmail to email a query result set in html format

I have the following:

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'email@address',
@body='None',
@subject ='Subject',
@body_format = 'HTML',
@profile_name = 'Profile',
@query ='set nocount on; exec dataDayStrip 657,4,7; set nocount off;',
@attach_query_result_as_file = 0,
@query_result_header = 1,
@query_result_width = 1000,
@exclude_query_output = 1,
@query_result_separator = ';'

This all works fine, except the email I receive includes the output of "dataDayStrip" procedure as unformatted and unreadable gobblygook.  I'd like to format it into HTML (or another format possibly).  How can I either do this automatically, or get a reference to the query result as a string to parse it manually or through another stored procedure? Or call the procedure and save the result set into a string and then parse that variable and then pass it along to the @body of the email.

The procedure that is being called uses dynamic sql and returns a simple resultset.
LVL 1
sapaincaAsked:
Who is Participating?
 
YveauCommented:
Here is something that should get you started ...

Hope this helps ...

create table Yveau (ID int, col1 varchar(25))
go
 
insert into Yveau values (1, 'First record')
insert into Yveau values (2, 'Second record')
insert into Yveau values (3, '... yep, 3rd :-)')
go
 
declare @cName varchar(128)
declare @cID int
declare @cCol1 varchar(25)
declare @cSQL1 varchar(max)
declare @cSQL2 varchar(max)
 
declare @SQL varchar(max)
 
declare cYveau cursor
for
select  name
from    sys.columns 
where   object_id = object_id('Yveau')
order   by column_id
 
open    cYveau
fetch   cYveau
into    @cName
 
while   (@@error = 0)
and     (@@fetch_status = 0)
begin
        select  @cSQL1 = IsNull(@cSQL1,'') + '<th>'+@cName+'</th>'
 
        fetch   cYveau
        into    @cName
end
 
close   cYveau
deallocate cYveau
 
declare cYveau cursor
for
select  ID, Col1
from    Yveau 
 
open    cYveau
fetch   cYveau
into    @cID
,       @cCol1
 
while   (@@error = 0)
and     (@@fetch_status = 0)
begin
        select  @cSQL2 = IsNull(@cSQL2,'') + '<td>'+cast(@cID as varchar(max))+'</td>'
        +       '<td>'+cast(@cCol1 as varchar(max))+'</td>'
 
        fetch   cYveau
        into    @cID
        ,       @cCol1
end
 
close   cYveau
deallocate cYveau
 
select  @SQL = '<table>'
+       '<tr>'
+       @cSQL1
+       '</tr>'
+       '<tr>'
+       @cSQL2
+       '</tr>'
+       '</table>'
from    yveau
 
select  @SQL
 
-->> Result:
<table><tr><th>ID</th><th>col1</th></tr><tr><td>1</td><td>First record</td><td>2</td><td>Second record</td><td>3</td><td>... yep, 3rd :-)</td></tr></table>

Open in new window

0
 
YveauCommented:
XML can be done automatically using the
    FOR XML AUTO
statement.

http://technet.microsoft.com/en-us/library/ms345117.aspx
http://technet.microsoft.com/en-us/library/ms345137.aspx

Hope this helps ...
0
 
sapaincaAuthor Commented:
Yveau,

This looks like it could very much be on the right track and a possible solution -- although any suggestions for generating an XML result set from a stored procedure?  The following note in one of the articles you suggested is discouraging: "Note   FOR XML continues to be a rowset aggregation clause of the SQL SELECT statement, and thus cannot transform side-effect output from stored procedures. If you want results of some form of processing transformed into XML, please use either a user-defined function or a view."

As I have full administrative rights to this database, I can re-write the stored procedures and/or create some new ones...that said, I'm not keen to change it to just return XML output, since it would need to return a normal result set in other circumstances.  As I mentioned in the initial post, the procedure produces a dynamic cross-tab result of arbitrary column width, so I don't think there's a way to avoid using a stored procedure for this.

Thanks
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
YveauCommented:
and what about a special view, would that be an option ?
0
 
sapaincaAuthor Commented:
Yveau,

I'm not familiar with a "special view"?  But anything that works is an option!
0
 
YveauCommented:
>> "... If you want results of some form of processing transformed into XML, please use either a user-defined function or a view."

... just wondering if you had figured it out ... not I guess :-)
how about a function or something ? I'll try to come up with one ...

0
 
sapaincaAuthor Commented:
Yveau,

Thank you greatly for the code and the help.  Very much appreciated.  Indeed, it's a successful answer so many thanks and full points.  

I've spent most of the day pursuing another solution, so I'm posting that below for future passers by.

What I was really looking for was a general-purpose solution with the aim of being able to email automated reports/result sets in the db mail system, being able to take any arbitrary query.  The motivation was this initial report, whereby the stored procedure could be modified to create a customized solution -- the dynamic sql definitely making it quite sticky.  Still, even a solution getting around it would mean that future views/querys/procs would need to be similarly created just to send formatted email, which isn't really ideal.

I therefore found a solution using the SQL Server 2006 CRL Integration such that I could pass an arbitrary SQL command as a string to this CRL stored procedure and have output of the command parsed and formatted into an HTML table.  There are a few quirks still, but it seems to be a solid solution and general purpose.  

The code is in C# and I downloaded and compiled it through MS's free Visual Studio C# Express application.

Seeing my original post, the email with the result set formatted into an HTML table  is now set doing the following:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@email',
@body='None',
@subject ='Subject',
@body_format = 'HTML',
@profile_name = 'Profile',
@query ='set nocount on; exec markets.dbo.toHTMLTable @sql=''exec markets.dbo.dataDayStrip 657,4,7''; set nocount off;',
@attach_query_result_as_file = 0,
@append_query_error = 1,
@query_no_truncate = 1,
@query_result_header = 0,
@exclude_query_output = 1,
@query_result_width = 32767


....

Deploying the CRL procedure was quite straight forward with the following commands:

CREATE ASSEMBLY toHTMLTable FROM 'E:\SQL Data\CLR\toHTMLTable1.dll'
CREATE PROCEDURE toHTMLTable @sql nvarchar(max) AS EXTERNAL NAME toHTMLTable.StoredProcedures.toHTMLTable

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
 
    public partial class StoredProcedures
    {   
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void toHTMLTable(String sql)
        {
 
            SqlConnection conn = new SqlConnection();
            SqlMetaData[] output = new SqlMetaData[1];
            SqlCommand cmd = new SqlCommand();
            SqlDataReader rdr = null;
            String html = "";
            int cols;
 
            conn.ConnectionString = "Context Connection=true";
            output[0] = new SqlMetaData("HTML", SqlDbType.Text);
            
            cmd.Connection = conn;
            cmd.CommandText = sql;
 
            conn.Open();
 
            rdr = cmd.ExecuteReader();
 
            cols = rdr.FieldCount;
            html += "<style type='text/css'>.header {font-weight:bold;white-space:nowrap;} .cell {vertical-align:top;text-align:center;}</style> \n";
            html += "<table cellpadding='5' cellspacing='0' style='font-family:verdana;font-size:10px;' border='1'> \n";
 
            html += "<tr> \n";
            for (int i = 0; i < cols; i++)
            {
                html += "<td class='header'>"+rdr.GetName(i)+ "</td> \n";
            }
            html += "</tr> \n";
 
            while (rdr.Read())
            {
                html += "<tr> \n";
                for (int i=0; i<cols; i++)
                {
                    html+= "<td class='cell'>"+rdr.GetValue(i).ToString()+"</td> \n";
                }
                html += "</tr> \n";
            }
            html += "</table> \n";
 
            SqlDataRecord oRows = new SqlDataRecord(output);
            oRows.SetString(0, html); 
            SqlContext.Pipe.Send(oRows);
 
            rdr.Close();
            conn.Close();
        }
    };

Open in new window

0
 
YveauCommented:
Cool ... you probably don't mind me taking this for a spin ?!
Thanks for the grade !
0
 
sapaincaAuthor Commented:
Yveau,

Yes, please feel free.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.