Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sp_send_dbmail

Posted on 2015-01-13
4
Medium Priority
?
149 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
[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
  • 2
  • 2
4 Comments
 
LVL 35

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 35

Accepted Solution

by:
ste5an earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
Suggested Courses

609 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