The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Solved
Sql Server 2008 Database Mail amends html tags
Posted on 2011-03-04
When I send a
I'm trying to send valid XHTML but of course
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
<br />
<br />
<br>
I'm trying to send valid XHTML but of course
<br>
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
[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
Active today
Please see my solution/code for a similar question:
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26826472.html
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26826472.html
I've tried that here is a code snippet
The meta tag and the br tag both have their self closing / removed.
Any other ideas please.
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/>
The meta tag and the br tag both have their self closing / removed.
Any other ideas please.
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
It is specifically these that I'm having a problem with
Many thanks
rs256
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.
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.
Active today
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
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
Active 4 days ago
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
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
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
Featured Post
Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen.
Visualize your data! ... really see it
To use the code to create a calendar from a q…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll
649 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.