Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

using sp_send_dbmail to email a query result set in html format

Posted on 2007-11-28
9
Medium Priority
?
12,468 Views
Last Modified: 2010-10-05
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.
0
Comment
Question by:sapainca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20365598
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
 
LVL 1

Author Comment

by:sapainca
ID: 20372872
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20388149
and what about a special view, would that be an option ?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:sapainca
ID: 20393613
Yveau,

I'm not familiar with a "special view"?  But anything that works is an option!
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20405886
>> "... 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
 
LVL 18

Accepted Solution

by:
Yveau earned 1500 total points
ID: 20406746
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
 
LVL 1

Author Comment

by:sapainca
ID: 20408023
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20409145
Cool ... you probably don't mind me taking this for a spin ?!
Thanks for the grade !
0
 
LVL 1

Author Comment

by:sapainca
ID: 20414360
Yveau,

Yes, please feel free.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question