Solved

Generating Email from T SQL in HTML format

Posted on 2013-06-04
5
655 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

695 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