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)='
<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 + '
What's rendered is "<a href=https://blah/reports_ssrs/report/subfolder1/subfolder stuff/report name?svcMeasure=8andsvcProv=brown>my measure"
Your message string is getting tokenized somewhere, replacing the ampersand with &. Not clear from your post where that might be. Start with this example, after changing the @recipients (and profile name if needed). You should get an e-mail with a clickable hyperlink to a URL that includes the ampersand. Then see what you are doing differently.
declare @MySQL as varchar(max)='<a href=' +
+ '&svcProv=' +'>' + 'abc'
@profile_name = NULL,
@recipients = 'email@example.com',
@body_format = 'HTML',
@body = @MySQL,
@subject = 'Test subject';
What I finally got to work was to create the url, but skip the & between parameters
Then parse it for the html with
td=cast(replace(rs2.measureUrl,'&','and') as xml), '',
Then just before I send the dbMail, do this
Probably not kosher, but it works! Thanks so much JamesK for the reply.