locked
Url not showing as Link RRS feed

  • 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='test@yahoo.com',
        @subject = @MailSubject,
        @body_format = 'HTML' ,
    	@body = @tableHTML
    end

    Thanks

    Tuesday, July 2, 2019 5:16 PM

Answers

  • 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='123@123.com',
    	@profile_name = 'mail_test' ,
        @subject = @MailSubject,
        @body_format = 'HTML' ,
    	@body = @tableHTML
    end

    Here is result of my demo:

    Best Regards,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 3, 2019 10:24 AM

All replies

  • 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='123@123.com',
    	@profile_name = 'mail_test' ,
        @subject = @MailSubject,
        @body_format = 'HTML' ,
    	@body = @tableHTML
    end

    Here is result of my demo:

    Best Regards,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 3, 2019 10:24 AM
  • 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).

    Wednesday, July 3, 2019 10:40 AM
  • User-797751191 posted

    Hi Patrice

      I have sql 2008

    Thanks

    Wednesday, July 3, 2019 11:01 AM
  • 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

    Wednesday, July 3, 2019 11:19 AM