How to diagnose SMTP conversation

[former_SQLA_member1694868]

I am trying to send an email from the server with an attachment using the system stored procedures xp_startsmtp and xp_sendmail.

begin
call xp_startsmtp(
smtp_sender = 'validsender@sendingdomain.com',
smtp_server = 'smtp.sendingdomain.com',
smtp_sender_name = 'SQL DB MSG');

call xp_sendmail(
recipient = 'validemail@validdomain.com',
subject = 'Hey Matt',
"message" = 'Just Checking in on you',
include_file = 'c:\\\\Test.txt');
end

This works when Test.txt is under a certain length like this:

abcdefghijklmno
abcdefghijk
test1
test2
test3
tes

This is either 44 characters or 49 characters depending on whether you count the new lines as characters.

Add one character to the above though to make either 45 or 50 respectively like this:

abcdefghijklmno
abcdefghijk
test1
test2
test3
test

and the message does not go through.

On a single line this works at 54 characters:

abcdefghijklmnoabcdefghijktest1test2test3test4test5tes

but this fails at 55

abcdefghijklmnoabcdefghijktest1test2test3test4test5test

Using a packet sniffer I can see the SMTP conversation and the server is responding with this:

Response: 451 see http://pobox.com/~djb/docs/smtplf.html

The website basically says that some mailers put LF at the end of lines (specifically mentioning the mailer "sendmail v8" doing this with long lines) instead of CR LF and that causes problems down the line.

The xp_sendmail procedure calls another procedure that calls a .dll, which I assume packages and sends the message with the attachment. Because it uses the .dll I do not think I can adjust the output manually by adding some CR 's somewhere.

So, is this a bug? Should I handle this differently?

Using 11.0.1.2436

[VolkerBarth]

Just very wild guesses: Does your Test.txt file contain LF or CR/LF pairs (cf. your other question...)? I have no solution but it could be that the attachment gets base64 encoded, and the interesting length behaviour might refer to the typical mail line length limit (72 characters IIRC) so that both the limit and an unexpected LF (without CR) form the problem. Have you tried to use the content_type parameter, eg. content_type = application/octet-stream?

[former_SQLA_member1694868]

The text file does not contain any viewable LF or CR/LF pairs, other than the ones that might be implicit as the lines change. It will be used with the file generated from my other question, but for testing I am using the above text typed into notepad and saved as txt. I tried specifying the content type and that failed as well with the same circumstances.
The documentation says that the include_file uses the application/octet-stream for the files, so I think the content_type only changes the message body type.

[Breck_Carter]

@Siger: Do you have access to a completely different SMTP server? Just to test to see if the behavior is different. Also, use a hex viewer to see what the actual line breaks are. Plus, 451 is used by some SMTP services to flag spam; see http://www.answersthatwork.com/Download_Area/ATW_Library/Networking/Network__3-SMTP_Server_Status_Co...

[graeme_perrow]

Can you email me the actual test.txt file and SQL script you are using? I have tried to reproduce this with the data you included in the question and I cannot. My email address is gperrow at sybase dot com.

[former_SQLA_member1694868]

Email sent. Thanks.

[graeme_perrow · Accepted solution]

Our base64 encoding did not conform exactly to the MIME standard. Most mail servers are flexible enough to accept it anyway, but the mail server Matt is using is very strict about it and will not accept our encoded data. I am in the process of fixing this and I will update this answer once I know what builds will contain the fix.

This is fixed in 10.0.1.4131, 11.0.1.2505, and 12.0.0.2594.

[former_SQLA_member1694868 · Accepted solution]

Thanks Graeme. We appreciate your work.

[VolkerBarth · Accepted solution]

And thanks for supplying the according build numbers - that's valuable information. (Oh, and being no system developer, I feel somewhat proud to have noticed the connection to base64, see my comments above...)

[Breck_Carter]

Check out http://www.answersthatwork.com/Download_Area/ATW_Library/Networking/Network__3-SMTP_Server_Status_Co...

...and let us know if that helped.