I have a proc where I generate small html doc with a link and send it out via xp_smtp_sendmail proc. Link is generated based on query results and is long. This works in most cases. However, sometimes the link gets broken due to spaces being inserted into querystring variable names, i.e. &Na me=John.
This might vary between email clients(same link works in Gmail, but might not work in comcast due to spaces. The space seems to be randomly inserted, so in each broken email link space might break other querystring variables. When I do PRINT from proc the link is clean, no spaces.
Here's my sample of the mail proc being executed within main proc(that gets query results and generates html for @Message). The space seems to be inserted regardless of whether I encode the url or not.
Thank you in advance for your help. I can send a cleaner version of the code if it's not displayed properly here.
....query results above
SET @Message = NULL
SET @Message = @Message +
+ '<br/>Dear ' + @FirstName + ' ' + @LastName + ','
+ '<br/><br/>Recently you took "' + @Title + '". '
+ 'In response to the question "What is it?" '
+ 'you responded "' + @Response + '".'
+ '<br/><br/>Following up on previous mailing'
+ '<br/><br/>Please click on the link below'
+ '<br/><br/><a href="' + @Link + '">Please click here</a>'
+ '<br/><br/>plain text'
+ '<br/><br/>plain text,'
+ '<br/><br/>plain text<br/>
plain text<br/>
plain text<br/>
plain text<br/>
plain text<br/>
plain text
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = '
[email protected]',
@FROM_NAME = 'Any User',
@TO = @Email,
@priority = N'NORMAL',
@subject = N'My email',
@message = @Message,
@messagefile = N'',
@type = N'text/html',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = 'smtp.server.any'
