dbMail parsing url ampersand to word "and"

Question

dbMail parsing url ampersand to word "and"

Janet Barnett

SSChasing Mays

Points: 604
More actions
October 10, 2018 at 1:07 pm

#380790

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 <a href='https://blah/reports_ssrs/report/subfolder1/subfolder stuff/report name?svcMeasure=8andsvcProv=brown'>my measure</a>

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

When sending HTML tables via DBMail I used the following:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = XYZProfileName',
    @recipients = 'Test@test.com',
    @subject = 'Your Subject Here',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

Regards,
Matt

Matt Simmons SSCrazy Points: 2846 More actions · Answer 2

Janet, I was able to test the following

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

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail_DHSSTSELIGDB01\TS2012_1',
    @recipients = 'matsimmons@deloitte.com',
    @subject = 'UATCR Assist WorkerWeb Exception Report',
    @body = @MySQL,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

The email I received had a hyperlink for SIMMONS and the hperlink was for "https://blah/Reports_SSRS/report/subfolder1/Subfolder%20Stuff/report%20Name?svcMeasure=1&svcProv=MATT"
I believe the @body_format will fix your issue.

Regards,
Matt

Janet Barnett SSChasing Mays Points: 604 More actions · Answer 3

I got the following to work, probably not kosher, but hey.
I created the url to put in the temp table like this: http://tada/folder/reportName?param1=blahparam2=blah
I then parse it for the html like this: td=cast(replace(rs2.measureURL,'&','and') as xml), '',
Then, just before I send the dbMail, I do this: set @tableHTML=replace(@tableHTML,'aram2','&param2=')