Solved

using sp_send_dbmail to email a query result set in html format

Posted on 2007-11-28
9
11,981 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
  • 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Compare data between two databases 16 95
Need help creating a stored procedure 4 56
Analysis of table use 7 41
Error when saving to sql table a '/' 5 25
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now