Generating Email from T SQL in HTML format

I need help sending out an automated email from SQL 2008 R2 server.  I can get the email to work just fine using the following syntax, but I want to customize it better.

USE msdb
 GO
EXEC sp_send_dbmail @profile_name='administrator',
@recipients='xxx@xyz.com',
@subject='New KB Articles have been added',
@execute_query_database = 'kbaccess',
@body='The following has been added in the past day',
@query = 'Select id,title,created_by from faq where created_on > GETDATE()- 1'

1.  When I email this report, it looks like crap.  After researching, it appears that I need to format the report as html.  I am having problems doing this.  The examples I have seen, I'm having problems inserting my variables into their example variables.  What I need is the above script to be send in a simple html table.  I'll worry about the fonts and colors later, just a simple HTML table would be good.  Please use my syntax above to help me write the exact script.

2.  Is there a way to have this report run only if results are returned?  Meaning if no rows are returned, can I have it cancel the email?

Thanks.
LVL 6
jonyeltonAsked:
Who is Participating?
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
You'll probably need to get familiar with the longest field in each of those three fields. I usually use their maximum length to define my varchar fields. This is only an example of what you may have to do:

DECLARE @body           varchar(2000),
        @query          varchar(2000),
        @maxlenid       varchar(8),
        @maxlentitle    varchar(8),
        @maxlencreate   varchar(8)

SELECT  @maxlenid = CONVERT(varchar(8), MAX(LEN(CONVERT(varchar(255), id)))),
        @maxlentitle = CONVERT(varchar(8), MAX(LEN(title)))
FROM    faq
WHERE   created_on > GETDATE() - 1

SET @body = '<p>New KB Articles have been added</p>
<pre style="white-space: nowrap; font-size: .75em;">'

SET @query = 'set nocount on;
select CONVERT(varchar(' + @maxlenid + '), id),
       CONVERT(varchar(' + @maxlentitle + '), title),
       CONVERT(varchar(10), created_by, 101)
from faq where created_on > GETDATE()- 1;
print ''</pre>'';'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'administrator',
    @recipients   = 'xxx@xyz.com',
    @subject      = 'New KB Articles have been added',
    @body         = @body,
    @body_format  = 'HTML',
    @query        = @query

Open in new window

Keep in mind, you may have to play around with this a bit, and since I coded it quickly, there could be a syntax error in it. Take time to look at it and get the gist of what it's accomplishing with those @maxlen variables, and how it creates the query with them.
0
 
dsackerContract ERP Admin/ConsultantCommented:
First, you'll want to add this to your EXEC parameters:

    @body_format  = 'HTML',

Open in new window

Next comes the fun part. You have to code the tags yourself, but I find that you can make it work best for you if you simply put in your @body parameter a few tags, ending it with the <PRE> tag. You then will need to end your query with the closing </PRE> tag.

Sound like fun? It's a bit cheezy, I admit, but it works. I suggest you do something like this:

DECLARE @body   varchar(2000),
        @query  varchar(2000)

SET @body = '<p>New KB Articles have been added</p>
<pre style="white-space: nowrap; font-size: .75em;">'

SET @query = 'set nocount on;
select id,title,created_by from faq where created_on > GETDATE()- 1;
print ''</pre>'';'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'administrator',
    @recipients   = 'xxx@xyz.com',
    @subject      = 'New KB Articles have been added',
    @body         = @body,
    @body_format  = 'HTML',
    @query        = @query

Open in new window

You may have to play with this a bit to get it to your liking. I had to make my columns smaller (for instance, make one of them CONVERT(varchar(8), SomeField)) to get them to all fit neatly.

But if you play with it a bit, you'll have a friendly email for your users.
0
 
dsackerContract ERP Admin/ConsultantCommented:
I corrected my post above. Had wrongly coded the @subject and @body parameters.
0
 
jonyeltonAuthor Commented:
I am able to successfully run the report below.  But it's still not right (in terms of the table).  How do I play around with the varchar field?  Can you give me an example of how I convert those three fields to 8 characters?
0
 
jonyeltonAuthor Commented:
I was able to get it with slight modification of the above code.
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.