Sv: Mercury 4.6 ./ MAISER / BASE64 and SQL 2005 Database Mail

PiS

posted Mar 11 '09 at 12:24 am

[quote user="tdawson"] We use SQL Server 2005 and are testing using Mercury to handle some simple mail lists for us. It appears that Mercury (or maybe the MAISER component) is having trouble with the BASE64 encoded emails generated by SQL Server 2005. [/quote]

You need to write a CLR-routine to send the message as plain text, or process the database mail-queue from an external "maintenanceapp". We've internally taken the second path, since the sql-2005 built-in mailer non changeable base-64 encoding gives problems with f.ex. squirrel mail and other clients as well, plus we can combine other actions outside the db in a separate app.

[quote user="tdawson"] We use SQL Server 2005 and are testing using Mercury to handle some simple mail lists for us.&nbsp;&nbsp; It appears that Mercury (or maybe the MAISER component) is having trouble with the BASE64 encoded emails generated by SQL Server 2005.&nbsp;[/quote] You need to write a CLR-routine to send the message as plain text, or process the database mail-queue from an external "maintenanceapp". We've internally taken the second path, since the sql-2005 built-in mailer&nbsp;non changeable&nbsp;base-64 encoding gives problems with f.ex. squirrel mail and other clients as well, plus we can combine other actions outside the db in a separate app.

tdawson

posted Mar 3 '09 at 2:15 am

Hi,

We use SQL Server 2005 and are testing using Mercury to handle some simple mail lists for us. It appears that Mercury (or maybe the MAISER component) is having trouble with the BASE64 encoded emails generated by SQL Server 2005. We are needing the SQL server to automatically subscribe users to mailing lists, hence the problem.

The following simple test (to generate a database mail command in SQL), produces the following as a session transcript into Mercury

EXEC msdb..sp_send_dbmail
@profile_name = 'Tim',
@recipients = 'lists@lists',
@body = 'list',
@body_format = 'TEXT',
@subject = 'test from dbmail'

11:31:04.390: Connection from 10.255.255.8, Tue Mar 03 11:31:04 2009<lf>
11:31:04.390: << 220 apollo.main ESMTP server ready.<cr><lf>
11:31:04.390: >> EHLO border<cr><lf>
11:31:04.390: << 250-apollo.main Hello border; ESMTPs are:<cr><lf>250-TIME<cr><lf>
11:31:04.390: << 250-SIZE 0<cr><lf>
11:31:04.390: << 250-8BITMIME<cr><lf>
11:31:04.390: << 250-AUTH CRAM-MD5 LOGIN<cr><lf>
11:31:04.390: << 250-AUTH=LOGIN<cr><lf>
11:31:04.406: << 250 HELP<cr><lf>
11:31:04.593: >> MAIL FROM:<tdawson@> SIZE=817 AUTH=<> BODY=7BIT<cr><lf>
11:31:04.593: << 250 Sender OK - send RCPTs.<cr><lf>
11:31:04.593: >> RCPT TO:<lists@lists><cr><lf>
11:31:04.609: << 250 Recipient OK - send RCPT or DATA.<cr><lf>
11:31:04.609: >> DATA<cr><lf>
11:31:04.609: << 354 OK, send data, end with CRLF.CRLF<cr><lf>
11:31:04.609: >> Received: from zeus (10.50.50.11) by mail (10.255.255.8) with<cr><lf>
11:31:04.609: >> Microsoft SMTP Server id 8.1.340.0; Tue, 3 Mar 2009 11:31:04 +1100<cr><lf>
11:31:04.609: >> Sensitivity: Normal<cr><lf>
11:31:04.625: >> Importance: Normal<cr><lf>
11:31:04.625: >> MIME-Version: 1.0<cr><lf>
11:31:04.625: >> From: "Tim (support)" <tdawson@><cr><lf>
11:31:04.625: >> To: <lists@lists><cr><lf>
11:31:04.640: >> Reply-To: tdawson@<cr><lf>
11:31:04.640: >> Date: Tue, 3 Mar 2009 11:31:04 +1100<cr><lf>
11:31:04.640: >> Subject: test from dbmail<cr><lf>
11:31:04.640: >> Content-Type: text/plain; charset="utf-8"<cr><lf>
11:31:04.640: >> Content-Transfer-Encoding: base64<cr><lf>
11:31:04.640: >> Message-ID: <a4351037-a324-417b-baf9-f468968879cb@HESTIA.main><cr><lf>
11:31:04.640: >> Return-Path: tdawson@<cr><lf>
11:31:04.640: >> <cr><lf>
11:31:04.656: >> bGlzdA==<cr><lf>
11:31:04.656: >> <cr><lf>
11:31:04.656: >> .<cr><lf>
11:31:04.656: << 250 Data received OK.<cr><lf>
11:31:04.656: >> QUIT<cr><lf>
11:31:04.671: << 221 apollo.main Service closing channel.<cr><lf>
11:31:04.671: --- Connection closed normally at Tue Mar 03 11:31:04 2009. ---
11:31:04.671:

MAISER log is:-
From tdawson@ at Tue, 3 Mar 2009 11:31:07 +1100
bGlzdA==

And the returned message is:

The mail server has encountered errors processing your request:
* Unrecognized command "".

Any Suggestions? Does Maiser support Base64?

Tim

Hi, We use SQL Server 2005 and are testing using Mercury to handle some simple mail lists for us.&nbsp;&nbsp; It appears that Mercury (or maybe the MAISER component) is having trouble with the BASE64 encoded emails generated by SQL Server 2005.&nbsp;&nbsp; We are needing the SQL server to automatically subscribe users to mailing lists, hence the problem. The following&nbsp;simple&nbsp;test (to generate a&nbsp;database mail command in SQL), produces the following as a session transcript into Mercury&nbsp; EXEC msdb..sp_send_dbmail @profile_name = 'Tim', @recipients = <A href="mailto:'lists@lists'">'lists@lists'</A>, @body = 'list', @body_format = 'TEXT', @subject = 'test from dbmail' 11:31:04.390: Connection from 10.255.255.8, Tue Mar 03 11:31:04 2009&lt;lf&gt; 11:31:04.390: &lt;&lt; 220 apollo.main ESMTP server ready.&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &gt;&gt; EHLO border&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-apollo.main Hello border; ESMTPs are:&lt;cr&gt;&lt;lf&gt;250-TIME&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-SIZE 0&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-8BITMIME&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-AUTH CRAM-MD5 LOGIN&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-AUTH=LOGIN&lt;cr&gt;&lt;lf&gt; 11:31:04.406: &lt;&lt; 250 HELP&lt;cr&gt;&lt;lf&gt; 11:31:04.593: &gt;&gt; MAIL FROM:&lt;tdawson@&gt; SIZE=817 AUTH=&lt;&gt; BODY=7BIT&lt;cr&gt;&lt;lf&gt; 11:31:04.593: &lt;&lt; 250 Sender OK - send RCPTs.&lt;cr&gt;&lt;lf&gt; 11:31:04.593: &gt;&gt; RCPT TO:&lt;<A href="mailto:lists@lists%3E%3Ccr%3E%3Clf">lists@lists&gt;&lt;cr&gt;&lt;lf</A>&gt; 11:31:04.609: &lt;&lt; 250 Recipient OK - send RCPT or DATA.&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt; DATA&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &lt;&lt; 354 OK, send data, end with CRLF.CRLF&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt; Received: from zeus (10.50.50.11) by mail (10.255.255.8) with&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt;&nbsp; Microsoft SMTP Server id 8.1.340.0; Tue, 3 Mar 2009 11:31:04 +1100&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt; Sensitivity: Normal&lt;cr&gt;&lt;lf&gt; 11:31:04.625: &gt;&gt; Importance: Normal&lt;cr&gt;&lt;lf&gt; 11:31:04.625: &gt;&gt; MIME-Version: 1.0&lt;cr&gt;&lt;lf&gt; 11:31:04.625: &gt;&gt; From: "Tim (support)" &lt;<A href="mailto:tdawson@%3E%3Ccr%3E%3Clf">tdawson@&gt;&lt;cr&gt;&lt;lf</A>&gt; 11:31:04.625: &gt;&gt; To: &lt;<A href="mailto:lists@lists%3E%3Ccr%3E%3Clf">lists@lists&gt;&lt;cr&gt;&lt;lf</A>&gt; 11:31:04.640: &gt;&gt; Reply-To: <A href="mailto:tdawson@%3Ccr%3E%3Clf">tdawson@&lt;cr&gt;&lt;lf</A>&gt; 11:31:04.640: &gt;&gt; Date: Tue, 3 Mar 2009 11:31:04 +1100&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Subject: test from dbmail&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Content-Type: text/plain; charset="utf-8"&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Content-Transfer-Encoding: base64&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Message-ID: &lt;<A href="mailto:a4351037-a324-417b-baf9-f468968879cb@HESTIA.main%3E%3Ccr%3E%3Clf">a4351037-a324-417b-baf9-f468968879cb@HESTIA.main&gt;&lt;cr&gt;&lt;lf</A>&gt; 11:31:04.640: &gt;&gt; Return-Path: <A href="mailto:tdawson@%3Ccr%3E%3Clf">tdawson@&lt;cr&gt;&lt;lf</A>&gt; 11:31:04.640: &gt;&gt; &lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; bGlzdA==&lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; &lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; .&lt;cr&gt;&lt;lf&gt; 11:31:04.656: &lt;&lt; 250 Data received OK.&lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; QUIT&lt;cr&gt;&lt;lf&gt; 11:31:04.671: &lt;&lt; 221 apollo.main Service closing channel.&lt;cr&gt;&lt;lf&gt; 11:31:04.671: --- Connection closed normally at Tue Mar 03 11:31:04 2009. --- 11:31:04.671: &nbsp; MAISER log is:- From <A href="mailto:tdawson@u">tdawson@</A> at Tue, 3 Mar 2009 11:31:07 +1100 &nbsp; bGlzdA== And the returned message is: The mail server has encountered errors processing your request: &nbsp;* Unrecognized command "". Any Suggestions?&nbsp; Does Maiser support Base64? Tim &nbsp;

Thomas R. Stephenson

posted Mar 3 '09 at 4:20 am

[quote user="tdawson"]
Hi,
We use SQL Server 2005 and are testing using Mercury to handle some simple mail lists for us. It appears that Mercury (or maybe the MAISER component) is having trouble with the BASE64 encoded emails generated by SQL Server 2005. We are needing the SQL server to automatically subscribe users to mailing lists, hence the problem.

The following simple test (to generate a database mail command in SQL), produces the following as a session transcript into Mercury
EXEC msdb..sp_send_dbmail
@profile_name = 'Tim',
@recipients = 'lists@lists',
@body = 'list',
@body_format = 'TEXT',
@subject = 'test from dbmail'
11:31:04.390: Connection from 10.255.255.8, Tue Mar 03 11:31:04 2009<lf>
11:31:04.390: << 220 apollo.main ESMTP server ready.<cr><lf>
11:31:04.390: >> EHLO border<cr><lf>
11:31:04.390: << 250-apollo.main Hello border; ESMTPs are:<cr><lf>250-TIME<cr><lf>
11:31:04.390: << 250-SIZE 0<cr><lf>
11:31:04.390: << 250-8BITMIME<cr><lf>
11:31:04.390: << 250-AUTH CRAM-MD5 LOGIN<cr><lf>
11:31:04.390: << 250-AUTH=LOGIN<cr><lf>
11:31:04.406: << 250 HELP<cr><lf>
11:31:04.593: >> MAIL FROM:<tdawson@> SIZE=817 AUTH=<> BODY=7BIT<cr><lf>

I have a problem here with the MAIL FROM. This is a bit confusing to me with the data after the SIZE command. I assume that the invalid email addresses yuo are using are not what you are sending.

11:31:04.593: << 250 Sender OK - send RCPTs.<cr><lf>
11:31:04.593: >> RCPT TO:<lists@lists><cr><lf>
11:31:04.609: << 250 Recipient OK - send RCPT or DATA.<cr><lf>
11:31:04.609: >> DATA<cr><lf>
11:31:04.609: << 354 OK, send data, end with CRLF.CRLF<cr><lf>
11:31:04.609: >> Received: from zeus (10.50.50.11) by mail (10.255.255.8) with<cr><lf>
11:31:04.609: >> Microsoft SMTP Server id 8.1.340.0; Tue, 3 Mar 2009 11:31:04 +1100<cr><lf>
11:31:04.609: >> Sensitivity: Normal<cr><lf>
11:31:04.625: >> Importance: Normal<cr><lf>
11:31:04.625: >> MIME-Version: 1.0<cr><lf>
11:31:04.625: >> From: "Tim (support)" <tdawson@><cr><lf>
11:31:04.625: >> To: <lists@lists><cr><lf>
11:31:04.640: >> Reply-To: tdawson@<cr><lf>
11:31:04.640: >> Date: Tue, 3 Mar 2009 11:31:04 +1100<cr><lf>
11:31:04.640: >> Subject: test from dbmail<cr><lf>
11:31:04.640: >> Content-Type: text/plain; charset="utf-8"<cr><lf>
11:31:04.640: >> Content-Transfer-Encoding: base64<cr><lf>
11:31:04.640: >> Message-ID: <a4351037-a324-417b-baf9-f468968879cb@HESTIA.main><cr><lf>
11:31:04.640: >> Return-Path: tdawson@<cr><lf>
11:31:04.640: >> <cr><lf>
11:31:04.656: >> bGlzdA==<cr><lf>
11:31:04.656: >> <cr><lf>
11:31:04.656: >> .<cr><lf>
11:31:04.656: << 250 Data received OK.<cr><lf>
11:31:04.656: >> QUIT<cr><lf>
11:31:04.671: << 221 apollo.main Service closing channel.<cr><lf>
11:31:04.671: --- Connection closed normally at Tue Mar 03 11:31:04 2009. ---
11:31:04.671:

However the message was properly received and the RFC 2822 message body looks normal.

MAISER log is:-
From tdawson@ at Tue, 3 Mar 2009 11:31:07 +1100
bGlzdA==
And the returned message is:
The mail server has encountered errors processing your request:
* Unrecognized command "".

Any Suggestions? Does Maiser support Base64

Tim

Mercury/32 generally does not even care what the body text looks like as long as it's a valid RFC 2822 message body. That said, when you are doing a subscribe or unsubscribe to the maiser email address you do need to be sending plain test messages.[/quote]

<blockquote>[quote user="tdawson"]Hi,We use SQL Server 2005 and are testing using Mercury to handle some simple mail lists for us.&nbsp;&nbsp; It appears that Mercury (or maybe the MAISER component) is having trouble with the BASE64 encoded emails generated by SQL Server 2005.&nbsp;&nbsp; We are needing the SQL server to automatically subscribe users to mailing lists, hence the problem. The following&nbsp;simple&nbsp;test (to generate a&nbsp;database mail command in SQL), produces the following as a session transcript into Mercury&nbsp;EXEC msdb..sp_send_dbmail @profile_name = 'Tim', @recipients = <a href="mailto:%27lists@lists%27" mce_href="mailto:'lists@lists'">'lists@lists'</a>, @body = 'list', @body_format = 'TEXT', @subject = 'test from dbmail'11:31:04.390: Connection from 10.255.255.8, Tue Mar 03 11:31:04 2009&lt;lf&gt; 11:31:04.390: &lt;&lt; 220 apollo.main ESMTP server ready.&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &gt;&gt; EHLO border&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-apollo.main Hello border; ESMTPs are:&lt;cr&gt;&lt;lf&gt;250-TIME&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-SIZE 0&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-8BITMIME&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-AUTH CRAM-MD5 LOGIN&lt;cr&gt;&lt;lf&gt; 11:31:04.390: &lt;&lt; 250-AUTH=LOGIN&lt;cr&gt;&lt;lf&gt; 11:31:04.406: &lt;&lt; 250 HELP&lt;cr&gt;&lt;lf&gt; 11:31:04.593: &gt;&gt; MAIL FROM:&lt;tdawson@&gt; SIZE=817 AUTH=&lt;&gt; BODY=7BIT&lt;cr&gt;&lt;lf&gt;</blockquote>I have a problem here with the MAIL FROM.&nbsp; This is a bit confusing to me with the data after the SIZE command. I assume that the invalid email addresses yuo are using are not what you are sending. <blockquote>11:31:04.593: &lt;&lt; 250 Sender OK - send RCPTs.&lt;cr&gt;&lt;lf&gt; 11:31:04.593: &gt;&gt; RCPT TO:&lt;<a href="mailto:lists@lists%3E%3Ccr%3E%3Clf" mce_href="mailto:lists@lists%3E%3Ccr%3E%3Clf">lists@lists&gt;&lt;cr&gt;&lt;lf</a>&gt; 11:31:04.609: &lt;&lt; 250 Recipient OK - send RCPT or DATA.&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt; DATA&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &lt;&lt; 354 OK, send data, end with CRLF.CRLF&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt; Received: from zeus (10.50.50.11) by mail (10.255.255.8) with&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt;&nbsp; Microsoft SMTP Server id 8.1.340.0; Tue, 3 Mar 2009 11:31:04 +1100&lt;cr&gt;&lt;lf&gt; 11:31:04.609: &gt;&gt; Sensitivity: Normal&lt;cr&gt;&lt;lf&gt; 11:31:04.625: &gt;&gt; Importance: Normal&lt;cr&gt;&lt;lf&gt; 11:31:04.625: &gt;&gt; MIME-Version: 1.0&lt;cr&gt;&lt;lf&gt; 11:31:04.625: &gt;&gt; From: "Tim (support)" &lt;<a href="mailto:tdawson@%3E%3Ccr%3E%3Clf" mce_href="mailto:tdawson@%3E%3Ccr%3E%3Clf">tdawson@&gt;&lt;cr&gt;&lt;lf</a>&gt; 11:31:04.625: &gt;&gt; To: &lt;<a href="mailto:lists@lists%3E%3Ccr%3E%3Clf" mce_href="mailto:lists@lists%3E%3Ccr%3E%3Clf">lists@lists&gt;&lt;cr&gt;&lt;lf</a>&gt; 11:31:04.640: &gt;&gt; Reply-To: <a href="mailto:tdawson@%3Ccr%3E%3Clf" mce_href="mailto:tdawson@%3Ccr%3E%3Clf">tdawson@&lt;cr&gt;&lt;lf</a>&gt; 11:31:04.640: &gt;&gt; Date: Tue, 3 Mar 2009 11:31:04 +1100&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Subject: test from dbmail&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Content-Type: text/plain; charset="utf-8"&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Content-Transfer-Encoding: base64&lt;cr&gt;&lt;lf&gt; 11:31:04.640: &gt;&gt; Message-ID: &lt;<a href="mailto:a4351037-a324-417b-baf9-f468968879cb@HESTIA.main%3E%3Ccr%3E%3Clf" mce_href="mailto:a4351037-a324-417b-baf9-f468968879cb@HESTIA.main%3E%3Ccr%3E%3Clf">a4351037-a324-417b-baf9-f468968879cb@HESTIA.main&gt;&lt;cr&gt;&lt;lf</a>&gt; 11:31:04.640: &gt;&gt; Return-Path: <a href="mailto:tdawson@%3Ccr%3E%3Clf" mce_href="mailto:tdawson@%3Ccr%3E%3Clf">tdawson@&lt;cr&gt;&lt;lf</a>&gt; 11:31:04.640: &gt;&gt; &lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; bGlzdA==&lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; &lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; .&lt;cr&gt;&lt;lf&gt; 11:31:04.656: &lt;&lt; 250 Data received OK.&lt;cr&gt;&lt;lf&gt; 11:31:04.656: &gt;&gt; QUIT&lt;cr&gt;&lt;lf&gt; 11:31:04.671: &lt;&lt; 221 apollo.main Service closing channel.&lt;cr&gt;&lt;lf&gt; 11:31:04.671: --- Connection closed normally at Tue Mar 03 11:31:04 2009. --- 11:31:04.671:&nbsp; </blockquote>However the message was properly received and the RFC 2822 message body looks normal.&nbsp;<blockquote>MAISER log is:- From <a href="mailto:tdawson@u" mce_href="mailto:tdawson@u">tdawson@</a> at Tue, 3 Mar 2009 11:31:07 +1100 &nbsp; bGlzdA== And the returned message is:The mail server has encountered errors processing your request: * Unrecognized command "". Any Suggestions?&nbsp; Does Maiser support Base64</blockquote> <blockquote>Tim</blockquote>Mercury/32 generally does not even care what the body text looks like as long as it's a valid RFC 2822 message body. That said, when you are doing a subscribe or unsubscribe to the maiser email address you do need to be sending plain test messages.[/quote]

Related Topics

Pending draft

Confirm move posts

Insufficient permissions

Select a different topic

Edit history