• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1068
  • Last Modified:

Formatting a clickable link in the body of a message using sp_send_dbmail

I am trying to send a message using sp_send_dbmail that has several fields, and one field that I would like to make clickable. This is the code I am currently using that I would have thought would work.

select td = '<a href="http://www.somewebsite.com">Login<a/>' + reportnumber from sometable for xml PATH('tr')

The problem is the link works fine, but is displayed as "http://www.somewebsite.com" instead of just "Login" as the code would suggest. The actual link I am using is far longer than that making the report unreadable unless I can shorten it down to just "Login" as it should act.

I do have the @body_format = 'HTML' set correctly...
0
bccops
Asked:
bccops
  • 3
1 Solution
 
Anthony PerkinsCommented:
If you look at the output you will see the the FOR XML is encoding your output as follows:
<tr>
  <td>&lt;a href="http://www.somewebsite.com"&gt;Login&lt;a/&gt;somereportnumber</td>
</tr>

Which I am sure is not what you intended.
0
 
bccopsAuthor Commented:
I have included the whole code sample to maybe this easier.
DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    '<H1>Response Time Report (Greater than 12 mins as test)</H1>' +
    '<table border="1">' +
    '<tr><th>AR</th><th>Time Created</th>' +
    '<th>Dispatched</th><th>Enroute</th><th>Arrival</th>' +
    '<th>Turnout</th><th>Travel</th><th>Address</th></tr>' +
    CAST ( ( SELECT td = '<a href="http://msdn.microsoft.com/en-us/library/bb545450.aspx">Login</a>' + right(reportnumber,5), '',
                    td = timecreate, '',
                    td = timedispatched, '',
                    td = timeenroute, '',
                    td = timearrive, '',
					td = datediff(ss,timedispatched,timeenroute), '',
					td = datediff(ss,timeenroute, timearrive), '',
                    td = address
              FROM cc_unittimes_ems
              WHERE timeenroute is not null
and timearrive is not null			
and datediff(ss, timecreate, timearrive) > 1200
and datediff(hh, timecreate, getdate()) < 224
				order by right(reportnumber,5) asc
                FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'Main',
	@recipients='nospam@email.com',
    @subject = 'Long Responses',
    @body = @tableHTML,
    @body_format = 'HTML' ;

Open in new window

0
 
bccopsAuthor Commented:
While I didnt catch it at first. I was given the answer in a roundabout way. The total solution was to add the following as a result of the improper format.

set @tableHTML = REPLACE( @tableHTML, '&lt;', '<' );
set @tableHTML = REPLACE( @tableHTML, '&gt;', '>' );
set @tableHTML = REPLACE( @tableHTML, '&amp;', '&' );

0
 
bccopsAuthor Commented:
Complete solution was to add

set @tableHTML = REPLACE( @tableHTML, '&lt;', '<' );
set @tableHTML = REPLACE( @tableHTML, '&gt;', '>' );
set @tableHTML = REPLACE( @tableHTML, '&amp;', '&' );
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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