Solved

using sp_send_dbmail to email a query result set in html format

Posted on 2007-11-28
9
11,944 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

8 Experts available now in Live!

Get 1:1 Help Now