Solved

sp_send_dbmail

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

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 34

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Results to Excel File 18 88
How to structure query with count aggregate 4 62
SQL to JSON 14 65
T-SQL: How to append a column for serialized JSON data? 2 49
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

734 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