Solved

Generating Email from T SQL in HTML format

Posted on 2013-06-04
5
613 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

867 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

22 Experts available now in Live!

Get 1:1 Help Now