Inserting a HTML Link Within a Table (SQL Mail)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Inserting a HTML Link Within a Table (SQL Mail)

Author

Topic

mpartridge
Starting Member

3 Posts

Posted - 2011-10-24 : 18:20:17

Hi all,
I'm helping a developer establish a framework and have run into a problem I can't resolve efficiently. We're using DB Mail to generate automated emails, the following is how DB Mail is called:

EXEC msdb..sp_send_dbmail
     @profile_name = 'DB Mail',
     @recipients = 'abc@xyz.com',
     @subject = 'HTML Test',
     @body_format = 'HTML',
     @body = @TableHTML

Here's where we are setting the value of @TableHTML:

SET @TableHTML = 
       N'<html><body>Please note the following outstanding items from the audit requirements listing, which you have requested. Please ensure that all items are updated to current status by 3 pm today.' +
      N'</br>' +
      N'<b><u>Overdue Items</u></b>' +
     N'<table border="1">' +
     N'<tr align="center"><b><td>Item #</td><td>Description</td><td>Suggested Due Date</td><td>Contact</td></b></tr>' +
     CAST ((SELECT [td/*] = ISNULL(ItemID,''), '',
      [td/*] = ISNULL(DocumentName,''), '',
      [td/*] = ISNULL(DueDate,''), '',
      [td/*] = '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''
     FROM dbo.DT_DocumentTracker WHERE RequestStatus IN('Open','Re-Open') AND DTTContact = @DTTContact FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
     N'</table>' +
     N'</body></html>'

The problem we have is with this line:

[td/*] = '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''

SQL will not recognise that as a HTML link and pastes the whole thing into a table cell, like this:
<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>

The link works (when mailto:abc@xyz.com is clicked a new email is opened with the To Address, CC and Subject fields filled out). However all that should appear in the table's cell is mailto:abc@xyz.com (as the link), not the underlying HTML code.

This works when it is not part of the table (when I put this in a separate line) which is fair enough. I've tried enclosing this line in it's own HTML tags but to no avail and have even played with including and excluding TYPE (part of FOR XML PATH). Any thoughts?

Thanks in advance.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-10-24 : 19:26:19

Please include some sample data for DT_DocumentTracker

If you don't have the passion to help people, you have no passion

mpartridge
Starting Member

3 Posts

Posted - 2011-10-24 : 19:53:40

Here are the first 5 rows:

ItemId DocumentName DueDate RequestStatus DTTContact
1 Appendix A 2011-09-30 In-progress TN
2 Appendix B 2011-09-30 Closed VA
3 Appendix C 2011-09-30 Closed VA
4 Appendix D 2011-09-30 Closed TN
5 Appendix E 2011-09-30 Closed VA

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-10-25 : 12:58:17

Ok so what would you like to appear in that td?

Could you provide sample data like this next time , you will get help much quicker?


declare @html table(ItemId int, DocumentName varchar(500), DueDate datetime, RequestStatus varchar(500), DTTContact varchar(500))
insert into @html
SELECT 
1,	 'Appendix A', '2011-09-30', 'In-progress',	 'TN'
UNION
SELECT 2,	 'Appendix B', '2011-09-30', 'Closed',	 'VA'
UNION
SELECT 3,	 'Appendix C', '2011-09-30', 'Closed',	 'VA'
UNION
SELECT 4,	 'Appendix D', '2011-09-30', 'Closed',	 'TN'
UNION
SELECT 5,	 'Appendix E', '2011-09-30', 'Closed',	 'VA'

If you don't have the passion to help people, you have no passion

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-25 : 19:32:09

Hello yes i know this error perfectly and Solution from my thoughts like that (using parameters)instead of direct strings
for example,But the next code done at application level only

 //update
                        myConn.Open();
                        SqlCommand myCommand = new SqlCommand("UpdateContents", myConn);
                        myCommand.CommandType = CommandType.StoredProcedure;
                        
                       //set Parameters
                        myCommand.Parameters.Add("@URLID", SqlDbType.Int);
                        myCommand.Parameters.Add("@OrgContent", SqlDbType.Text);

                        myCommand.Parameters["@OrgContent"].Value = Body;
                        myCommand.Parameters["@URLID"].Value = value;
                       
                        try
                        {
                            RowAffected = myCommand.ExecuteNonQuery();
                        }

paul Tech

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources