[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

Sql Server 2008 Database Mail amends html tags

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
rs256
Asked:
rs256
  • 5
  • 2
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
0
 
rs256Author Commented:
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
 
rs256Author Commented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Anthony PerkinsCommented:
Have you considered sending the email as text instead of HTML?
0
 
rs256Author Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
Anthony PerkinsCommented:
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
 
rs256Author Commented:
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
 
rs256Author Commented:
Really appreciate the help from both all parties.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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