Solved

Generating Email from T SQL in HTML format

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 45
2016 SQL Licensing 7 41
SQL Server 2012 r2 - Varible Table 3 24
New to SSRS, extremely slow running report. 8 20
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 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