• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

sp_send_dbmail

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
Jeff_Kingston
Asked:
Jeff_Kingston
  • 2
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
I'll accept that, but what would you suggest as a method ti insert a variable number of lines?
0
 
ste5anSenior DeveloperCommented:
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
 
Jeff_KingstonAuthor Commented:
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

Independent Software Vendors: 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!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now