SQL Mail Parses ampersand within a url as "and"

Transact-SQL
1

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"

2

Your message string is getting tokenized somewhere, replacing the ampersand with &amp. 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='  + 
	'https://blah/Reports_SSRS/report/subfolder1/Subfolder%20Stuff/report%20Name?svcMeasure=' 
	+ '&svcProv=' +'>' + 'abc' 
EXEC msdb.dbo.sp_send_dbmail
			@profile_name = NULL,
			@recipients = 'someaddress@somecompany.com',
			@body_format = 'HTML',
			@body = @MySQL,
			@subject = 'Test subject';
3

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! Thanks so much JamesK for the reply.