Solved

sp_send_dbmail

Posted on 2015-01-13
4
142 Views
Last Modified: 2015-01-13
We currently use send_dbmail to remind users of upcoming seminars or webcasts that they have registered for, replacing variables in an HTML document with information from database ie: date, time, description etc etc.


Is it possible to define a scrolling region in an HTML document and then insert a variable number of records into the template?

I'd love to see an example of the SQL if it is possible.
0
Comment
Question by:Jeff_Kingston
  • 2
  • 2
4 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40546652
Yes, it's possible.

But does it makes sense? No, cause the different e-mail readers interpret different subsets of HTML/CSS. So you may to get it work in one client, but not the other.
0
 

Author Comment

by:Jeff_Kingston
ID: 40546664
I'll accept that, but what would you suggest as a method ti insert a variable number of lines?
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40547110
Use a table and FOR XML. E.g.  

DECLARE @Template NVARCHAR(MAX) = N'
<html>
	<body>
		<table>						
			<!-- CONTENT -->
		</table>
	</body>
</html>
';

-- Constants.
DECLARE @CONTENT_TAG NVARCHAR(255) = N'<!-- CONTENT -->';
DECLARE @LINE_BREAK NVARCHAR(255) = N'<br />';
DECLARE @PROFILE_NAME NVARCHAR(255) = N'ProfileName';
DECLARE @SUBJECT NVARCHAR(255) = 'Subject';

-- Variables.
DECLARE @Body NVARCHAR(MAX) = '';    
DECLARE @Recipients NVARCHAR(MAX) = 'here@it.goes';
	
WITH    Data
          AS ( SELECT   T.name ,
                        T.type_desc ,
                        T.create_date
               FROM     sys.tables T
             )
    SELECT  @Body += ( SELECT   D.name AS td ,
                                NULL ,
                                type_desc AS td ,
                                NULL ,
                                D.create_date AS td
                       FROM     Data D
                       ORDER BY D.create_date
					   FOR XML PATH('tr') , ROOT('tbody')
                     );
		     
SET @Body = REPLACE(@Template, @CONTENT_TAG, @Body);		

EXECUTE msdb.dbo.sp_send_dbmail @profile_name = @PROFILE_NAME, @recipients = @Recipients, @body = @Body, @subject = @SUBJECT, @body_format = 'HTML';

Open in new window

0
 

Author Closing Comment

by:Jeff_Kingston
ID: 40547418
This seems to do what I want.  Appreciate the quick reply and the sample code that I'm sure will require
a tweak or two
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

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