xp_smtp_sendmail blank space added to html randomly

Question

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'

Answer 1

How long is the link with values?

My guess is that some wrapping occurs and you have a the line break changing to a space.

It appears random because of values are different lengths so the split happens randomly.

Another thought: you only need <br> tags. <br /> is not needed (with or without space) and may contribute

Answer 2

How about placing carriage returns into your coding. BR will only 'carriage' return in HTML but the source itself does not have carriage returns in.

Answer 3

1 - Check the length of the message you generate and switch to sending from a file it the length comes close to 4000 char.

2 - Inject cr/lf chars before every sentence or paragraph.

3 - Make sure that you have cr/lf before and after a tag opening (or just lf if that's what smtp server likes)

4 - Talk to whoever you have to about shortening the length of that embedded link - it's plain unsustainable

5 - Ask about any configuration options on the smtp server to let you send e-mail as fully encoded binary (requires MIME headers and Base64 encoding)

6 - Look for a more recent alternative to xp_smtp_sendmail, a .NET xp perhaps

Answer 4

You problem is you're treating mai like a web page. Mail was invented in the time of text only consoles, and often have 72 character line length limit. You need to wrap your HTML in lines no longer than 72 chars to ensure it's delivered properly to all recipients.

To do this, use quoted printable encoding and wrap the lines to max 72 characters.

However, I have no idea how to do this in SQL Server, other than writing a procedure for the job. You can follow the advice in the ASP FAQ.

Basically what it says is shorten the link either by tricks or by using link shortening services.