Url not showing as Link

Question

User-797751191 posted

Hi

 In Gmail it is showing link as - <a href='http://localhost:1501/Test.aspx?No=990093'>Click Me</a>. But in Yahoo Mail it is not showing link

  Body format is already html . Below is the code

begin
Set @MailHeader='<H4>Dear Sir,<br/><br/> Email <br/><br/></H4>';

SET @tableHTML =@MailHeader+
    N'<table border="1" >' +
    N'<tr><th>SN.</th><th>Document No</th><th>Date</th>' +
    N'<th>Remarks</th><th>Url</th>' +
 
    CAST ( ( SELECT td = ROW_NUMBER() OVER(ORDER BY [Date]), '',
					td = [Invoice No], '',
					td = [Date], '', 
					td = [Remarks], '', 
					td = +'<a href="http://localhost:1501/Test.aspx?No='+CAST(@ENo AS varchar(10))+'">Click Me</a>'
              FROM [Test]
			  where [ENo] = @ENo
			  order by [Date]
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table><br/><br/>Thanks and Regards<br><br> ;

	EXEC msdb.dbo.sp_send_dbmail @recipients='[e-mail]',
    @subject = @MailSubject,
    @body_format = 'HTML' ,
	@body = @tableHTML
end

Thanks

Answer 1

User-719153870 posted

Hi jsshivalik,

When you try to convert text to XML and then to html, "<" and ">" are converted to "& lt;" and "& gt;"

I recommend that you use the replace function to replace "& lt;" and "& gt;" with "<" and ">".

You can refer to below codes:

declare @MailHeader varchar(200)
declare @tableHTML varchar(2000)
declare @ENo int
set @ENo=10001
declare @MailSubject varchar(200)
use ddd
begin
IF EXISTS (
    SELECT 1 FROM sys.configurations 
    WHERE NAME = 'Database Mail XPs' AND VALUE = 0)
BEGIN
  PRINT 'Enabling Database Mail XPs'
  EXEC sp_configure 'show advanced options', 1;  
  RECONFIGURE
  EXEC sp_configure 'Database Mail XPs', 1;  
  RECONFIGURE  
END
Set @MailHeader='<H4>Dear Sir,<br/><br/> Email <br/><br/></H4>';

SET @tableHTML =@MailHeader+
    N'<table border="1" >' +
    N'<tr><th>SN.</th><th>Document No</th><th>Date</th>' +
    N'<th>Remarks</th><th>Url</th>' +


       replace(REPLACE( CAST ( ( SELECT td = ROW_NUMBER() OVER(ORDER BY [Date]), '',
					td = [Invoice No], '',
					td = [Date], '', 
					td = [Remarks], '', 
					td = +N'<a href="http://localhost:1501/Test.aspx?No='+CAST(@ENo AS varchar(10))+N'">Click Me</a>'
              FROM [Test]
			  where [ENo] = @ENo
			  order by [Date]
              FOR XML PATH('tr'), TYPE 
    ) AS VARCHAR(MAX) ),'&lt;','<'),'&gt;','>') +
    N'</table><br/><br/>Thanks and Regards<br><br>' ;

	EXEC msdb.dbo.sp_send_dbmail 
	@recipients='[e-mail]',
	@profile_name = 'mail_test' ,
    @subject = @MailSubject,
    @body_format = 'HTML' ,
	@body = @tableHTML
end

Here is result of my demo:

Best Regards,

Yang Shen

Answer 2

User753101303 posted

Hi,

If using SQL Server 2017 I noticed new string functions such as STRING_AGG and CONCAT_WS which could be better than the use of the XML feature (which causes < and > to be encoded).

Answer 3

User-797751191 posted

Hi Patrice

  I have sql 2008

Thanks

Answer 4

User753101303 posted

So try Yang Shen solution. It doesn't work ?

The problem is using a trick whose goal is to create XML documents (in which < and > characters are encoded). Keep just in mind that from SQL Server 2017 you should be able to replace this approach with something simpler...

Another known trick than using FOR XML is to use something such as :

DECLARE @html VARCHAR(max)
SET @html=''
SELECT @html=@html+'<tr><td>'+name+'</td></tr>'+CHAR(13) FROM sys.databases
IF @html<>''
BEGIN
	-- We got something from this table, add to a @body etc...
	PRINT @html
END

I believe that with SQL Server 2017 it might be something such as :

SELECT @html=STRING_AGG('<tr><td>'+CONCAT_WS('</td><td>',col1,col2,col3)+'</td></tr>') FROM sys.database