xp_smtp_sendmail blank space added to html randomly

Posted by Igor Timofeyev on Stack Overflow See other posts from Stack Overflow or by Igor Timofeyev
Published on 2010-02-23T00:05:28Z Indexed on 2010/03/20 20:11 UTC
Read the original article Hit count: 472

Filed under:
|
|

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'

© Stack Overflow or respective owner

Related posts about sql

Related posts about html