Solved

sp_send_dbmail

Posted on 2015-01-13
4
144 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

740 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