CODEWITHSUNDEEP
sqlreporting-servicesampersanddbmail
Janet Barnett
Janet Barnett

Reputation: 111

dbMail concatenated url parsing ampersand as word and

I'm using dbmail to gather data and send an email. As a part of that email, I have a url surrounding text for a clickable link to an SSRS report with parameters. If I only have one parameter, I'm fine. But, if I add parameters, the url is passed with the ampersand being replaced by "and". I've tried the text &, char(38), and text %26 and it parses it the same by converting it to the word "and".

For example, if the url is collated using

declare @MySQL as varchar(max)='<table><tr><td>
<a href=' + char(39) + 'https://blah/Reports_SSRS/report/subfolder1/Subfolder%20Stuff/report%20Name?svcMeasure=' + cast(met.measureID as varchar(2)) + '&svcProv=' + z.abbrv + char(39) +'>' + measureTitle + '</a></td></tr></table>'

What's rendered is https://blah/reports_ssrs/report/subfolder1/subfolder stuff/report name?svcMeasure=8andsvcProv=brown'>my measure

     set @tableHTML = @tableHTML + 
 N'<table border="1" cellspacing="1" cellpadding="2"><tr><td colspan="5" bgcolor="#68cbd0"><b>Table Title</b> </td></tr>' +
 CAST((SELECT  case when rs2.myBG='a' then '#F0FFF0' else '#FFC0CB' end  as [@bgcolor], 
 td  = cast(rs2.measureUrl as xml), '',  
 'center' as 'td/@align',td = '(of '+ convert(varchar(5),rs2.myPeerCount) +')',''  
 from (blah blah blah)rs2 FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +  N'</table>';

Upvotes: 0

Views: 88

Answers (2)

Janet Barnett
Janet Barnett

Reputation: 111

What I finally got to work was to create the url, but skip the & between parameters 

http://tada/folder/reportName?param1=blahparam2=blah

Then parse it for the html with 

td=cast(replace(rs2.measureUrl,'&','and') as xml), '',

Then just before I send the dbMail, do this 

set @tableHTML=replace(@tableHTML,'param2','&param2=')

Probably not kosher, but it works!

Upvotes: 0

Danial Wayne
Danial Wayne

Reputation: 348

Use the &amp; html version in place of &

    declare @MySQL as varchar(max)='<table><tr><td>
    <a href=' + char(39) + 'https://blah/Reports_SSRS/report/subfolder1/Subfolder%20Stuff/report%20Name?svcMeasure=' + cast(met.measureID as varchar(2)) + '&amp;svcProv=' + z.abbrv + char(39) +'>' + measureTitle + '</a></td></tr></table>'

Upvotes: 0

Related Questions