BUG: SQLMail: sp_processmail Returns Failure Code But No Error

• Microsoft SQL Server, version 6.0

SYMPTOMS

The sp_processmail procedure will return a value of 1 to indicate failure, however, it does not return any error messages to indicate the cause of the failure. Under some circumstances, sp_processmail may fail to run successfully even though SQLMail is properly set up and there is one or more messages in the inbox to be processed.

CAUSE

If the originator of a mail message in SQL Server's inbox is a mail username that cannot be resolved to a valid mail address, sp_processmail will fail.

WORKAROUND

You can do one of the following to work around this problem:

1. Maintain a table in SQL Server containing mail usernames and their corresponding mail addresses. Rewrite sp_processmail to look up the user's address and supply that when calling xp_sendmail.

2. Have users include their mail address on the subject line of the message. Rewrite sp_processmail to supply that address when calling xp_sendmail.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

You can tell if the mail username can be successfully resolved to a mail address by running xp_sendmail to send a message to that user. If the username cannot be successfully resolved to a mail address, xp_sendmail will return the following error:

   Msg 17921, Level 18, State 1
   A recipient was specified ambiguously

   EXEC xp_sendmail "SQL", "Test message"

The mail address would be of the form [MS:network/postoffice/mailbox], such as the following:

   EXEC xp_sendmail "[MS:Windows/scotthu/sql]", "Test message"