?
Solved

Sql Server 2008 Database Mail amends html tags

Posted on 2011-03-04
9
Medium Priority
?
761 Views
Last Modified: 2012-06-21
When I send a
<br />

Open in new window

tag or indeed any self closing tag the source of the message gets altered by SQL Server Mail and the final / is removed from the tag i.e
<br />

Open in new window

becomes
<br>

Open in new window

.

I'm trying to send valid XHTML but of course
<br>

Open in new window

is not a valid for this DTD.

I've tried escaping the / and using the CHAR function both of which make no difference.

I assume it's go something to do with the way Database Mail has been set-up but I can't find anything related to this sort of thing.

Please help
0
Comment
Question by:rs256
[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
  • 5
  • 2
  • 2
9 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35039850
0
 

Author Comment

by:rs256
ID: 35042971
I've tried that here is a code snippet

		SET @BodyHTML =
		  N'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
			<html>
		 		<head>
					<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
		 			<title> + @MessageSubject + </title>
					<style type="text/css">
						body
						{ 
							font-family: Verdana, Arial, Helvetica, sans-serif;
							font-size: 12px;
							color: #333;
						}
					</style>
				</head>
				<body><br/>

Open in new window


The meta tag and the br tag both have their self closing / removed.

Any other ideas please.
0
 

Author Comment

by:rs256
ID: 35045179
Just one other thing to add lcohan, your example doesn't have any self closing tags.

It is specifically these that I'm having a problem with

Many thanks

rs256

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35047132
Have you considered sending the email as text instead of HTML?
0
 

Author Comment

by:rs256
ID: 35054910
Yes, however we are sending email with a table in it with 20-30 records in it and it works a lot better in HTML.

I understand all real coders only send text messages with links in but this is a customer facing system and HTML is pretty much expected !

Thanks for the thought though.

0
 
LVL 40

Expert Comment

by:lcohan
ID: 35058496
We are at least 2 individuals in this comunity that tested and agreed the code below works fine for sending HTML - maybe you missed something with copy paste:

create table #test (column1 int, column2 text, column3 text, column4 text)
insert into #test SELECT 1 ,'text2','text3','text4'
insert into #test SELECT 2 ,'code2','code3','code4'


DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)

SET @title = 'Summary Report - FunctionCode'
SET @tableHTML =
    N'<H1>' + @title + '</H1>' +
    N'<table border="1">' +
    N'<tr>' +
      N'<th>Column1</th>' +
      N'<th>Column2</th>' +
      N'<th>Column3</th>' +
      N'<th>Column4</th>' +
     CAST(
            (SELECT
                  td = Column1 ,'',
                  td = Column2,'',
                  td = Column3,'',
                  td = Column4,''
            FROM  #test WHERE column1 in (1,2)
            FOR XML PATH('tr'), TYPE)
       AS NVARCHAR(MAX) )
       + N'</table>' ;

IF @tableHTML is not null
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'mail_profile',
            @recipients='user@mail.com',
            @subject = @title,
            @body = @tableHTML ,
            @body_format = 'HTML' ;
END

drop table #test
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35060054
Something else is going on here.  I just executed the code below and verified the html sent (using sysmail_sentitems) and received matched what was originally entered.
DECLARE @rc int,
	@Message varchar(1000)

SET @Message = '<html>
			<body bgColor=#ffffff>
				Test Email. <br />
			</body>
		</html>'
EXEC @rc = msdb.dbo.sp_send_dbmail 
	    @recipients = 'my email address goes here', 
	    @subject = 'Test Email',
	    @body_format = 'HTML', 
	    @body = @Message
	
SELECT  @rc

Open in new window

0
 

Author Comment

by:rs256
ID: 35068016
Many thanks for your efforts lcohan and acperkins.  lcohan's example still doesn't have any self closing tags but acperkins got to the bottom of it.

It's not database mail that is causing the problem it's Exchange 2010, although Gmail seems to do the same thing we did find some mail mails servers that left the tags alone.

This takes the issue out of SQL database mail territory so I'll close it and accept acperkins's answer.

Thanks again for the help.

rs256
0
 

Author Closing Comment

by:rs256
ID: 35068019
Really appreciate the help from both all parties.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

764 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