Solved

Generating Email from T SQL in HTML format

Posted on 2013-06-04
5
632 Views
Last Modified: 2013-06-04
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.
0
Comment
Question by:jonyelton
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39220010
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39220027
I corrected my post above. Had wrongly coded the @subject and @body parameters.
0
 
LVL 6

Author Comment

by:jonyelton
ID: 39220224
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
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 39220246
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
 
LVL 6

Author Closing Comment

by:jonyelton
ID: 39220467
I was able to get it with slight modification of the above code.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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