INF: Common SQL Mail Problems (315886)

MORE INFORMATION

Check Your Configuration

Correcting an improper configuration can resolve the vast majority of SQL Mail problems. To make sure that you have SQL Mail configured correctly, use this list:

• You must have a mail client (such as Microsoft Outlook) installed on the computer that runs SQL Server.
• Use the Corporate or Workgroup option for Microsoft Outlook 2000 (Microsoft Outlook 2002 does not have the Corporate or Workgroup option). To select the option, on the Tools menu, click Options, and then double-click the Mail Services tab. In the Mail Services dialog box, click the Mail Support command. Microsoft Outlook 2002 does not work with SQL Mail when you use an Internet mail server unless the Outlook client is left running at all times.
• You must have a working mail server such as Microsoft Exchange or an Internet mail (Post Office Protocol 3 [POP3] or Simple Mail Transport Protocol [SMTP]) server. The outgoing mail SMTP server need not be the same as the incoming mail POP3 server; however, you must have a valid incoming mail server with a configured mail account, even if you never plan to send mail to SQL Mail.
• The account you use to start the SQL Server service must be a domain account if you are using an Exchange server. The domain account must have a mailbox on the Exchange server. If you are using an Internet mail server, you can use a local account to start SQL Server. However, the account you use to start SQL Server cannot be the Local System account, under any circumstances. The same applies to the service account you use to start the SQL Agent if you are using SQLAgentMail.
• Because mail profiles are associated with a Microsoft Windows login account, you must log on to the computer that is running SQL Server with the SQL Server service startup account in order to inspect the mail profile available to SQL Server. For example, open Control Panel. In the Control Panel dialog box, double-click Mail. In the Mail dialog box, double-click Show Profiles. If you are using SQLAgentMail and the SQL Agent service account is different from the SQL Server service account, you must also log on with the SQL Agent service startup account to inspect the mail profiles available to the SQL Agent.
• You must be able to open the mail client and test sending and receiving an e-mail to your mail server. No dialog box or message should appear that requires user action. SQL Mail uses the mail client to send mail. Because SQL Mail runs in the SQL Server service, any dialog boxes that require user interaction appear on the "virtual desktop" and do not receive a response. The lack of response causes SQL Mail to stop responding (hang).
• You must provide SQL Mail the mail profile you use to test your mail client configuration. To do so, in Enterprise Manger, open the Support Services folder. In the Support Services folder, right-click SQL Mail, and then click Properties. If you use SQLAgentMail, you must provide the mail profile to SQL Agent. To do so, in Enterprise Manager, open the Management folder, right-click SQL Agent, and then click Properties.
• The default Internet e-mail program must be the same as the mail client you use to test the mail configuration. To make sure you have the correct program, open Control Panel, double-click Internet Options, and then click the Programs tab.
• For any of the preceding changes to take effect, you must stop, and then restart the SQL Server service. If you reconfigure the default mail profile, you must restart the computer for the changes to take effect.

Frequently Encountered Problems

A list of SQL Mail-related problems frequently encountered by customers follows. Discussion of the various problems is in a question and answer format. The questions are not listed in order of frequency although similar questions are grouped together. The answers do not account for every possible cause; however, they do reflect the major causes of a particular problem.

Q1: Why did SQL Mail stop working after I upgraded to SQL Server 2000?

A1:SQL Server 2000 introduces significant changes to SQL Mail. The major change is that SQL Mail now uses the extended Mail Application Programming Interface (MAPI) instead of simple MAPI. For additional information about the differences between simple and extended MAPI, click the article number below to view the article in the Microsoft Knowledge Base: If you are using SQL Server 2000, Microsoft strongly encourages you to apply Microsoft SQL Server 2000 Service Pack 2 (SP2), or later, before you try to use SQL Mail.

Q2: Why did SQL Mail stop working after I upgraded Microsoft Outlook or applied the Outlook security patch?

A2: An Outlook 2000 E-Mail Security Update released in August 2001 prevents the spread of e-mail worms that propagate by sending themselves to everyone in your address book. The security patch detects when a non-Outlook program sends an e-mail and a dialog box with this message appears: You must click Yes if you want to send the mail.

SQL Mail for SQL Server 6.5 and SQL Server 7.0 is treated as a non-Outlook program that is attempting to send mail. However, because SQL Mail is called in the SQL Server service, the dialog box is sent to the "virtual desktop" of the SQL Server service and does not appear on the server. Because no user interaction occurs, SQL Mail stops responding.

The security features can be relaxed with the assistance of your Exchange administrator; however, a better solution is to upgrade to SQL Server 2000. SQL Mail in SQL Server 2000 makes an extended MAPI connection, which bypasses the security dialog box. For additional information on the Outlook security patch, click the article number below to view the article in the Microsoft Knowledge Base: Microsoft Outlook 2002 includes the security features found in the Outlook 2000 security update.

If you are already using SQL Server 2000 and SQL Mail stops working after you upgrade to Outlook 2002, you are probably using an Internet Mail server. You could configure previous versions of Outlook so that you could send mail from a Microsoft Windows service. To improve the performance when working with non-Exchange mail, the mail spooler runs in the process space of Microsoft Outlook 2002, which requires the Outlook client to be open at all times when you send mail to a non-Exchange server. If you do not want to leave the Outlook 2002 client running on your server at all times (requiring you to be logged on to the server), you must use an Exchange server as your mail server or roll back to a previous version of Microsoft Outlook.

Q3: How do I know if I have the Outlook security patch installed on my server?

A3: On the Outlook Help menu, click About Microsoft Outlook. If your version of Outlook 2000 is 9.0.0.4201 or later, then you have the Outlook E-mail Security Update installed.

Q4: How can I prevent SQL Mail from hanging if I have the Outlook security patch or I'm using Outlook 2002?

A4: You must work with your Exchange administrator to change the default security settings. The exact procedure depends on whether you are using Outlook 2000 or Outlook 2002 (provided with Office XP). For additional information about changing the Exchange security settings, click the article numbers below to view the articles in the Microsoft Knowledge Base: Q5: Why is my outgoing mail stuck in the Inbox?

A5: This problem is more likely to occur with Outlook 2002. To improve Internet e-mail performance, the Office development team moved the mail spooler for non-Exchange mail into the Outlook process space. You must have the Outlook client open to send outgoing e-mail. This problem does not affect the e-mail that Outlooks sends to the Exchange server.

You may also encounter this problem when you use Outlook 2000. In this case, the most likely cause is that you did not provide a working POP3 server as the incoming mail server in the mail profile.

Q6: Why is my outgoing mail stuck in the Outbox?

A6: This problem occurs frequently with older e-mail clients like Microsoft Windows Messaging and Microsoft Outlook 97. These mail clients were never intended to be called from a Windows service such as SQL Server. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: These mail clients are now obsolete, so you should no longer be encountering this problem. However, if you are, you should be able to obtain a hotfix by contacting Microsoft Product Support Services.

Q7: Why do I not have any sent messages in the Sent Items folder?

A7: SQL Mail for SQL Server 6.5 and SQL Server 7.0 saves a copy of sent messages in the Sent Items folder of Outlook, which is the default behavior with the simple MAPI interface. Even if you disable this option by clearing the Save copies of messages in Sent Items folder option in the Outlook mail client, the option is ignored.

SQL Mail for SQL Server 2000 is written using the extended MAPI interface and does not save a copy of sent messages in the Sent Items folder. If you need a copy of the messages that SQL Mail sends from SQL Server 2000, you must cc: them to yourself and create an Outlook message rule that routes these message copies to your Sent Items folder.

Q8: Why is my mailbox full but I do not see any mail in the Sent Items folder?

A8: In SQL Server 7.0 and SQL Server 2000, there is an option in the SQL Server Agent Properties window to Save copies of the sent messages in the 'Sent Items' folder. If you do not select that option SQL Mail saves copies of the SQL Server Agent mail in the root folder of the Exchange server. When the mailbox becomes full, the xp_sendmail stored procedure may fail to send mail and return error 0x80040403 or the MAPI_E_TABLE_TOO_BIG error message, among others.

You will not be able to see the copies of the SQLAgentMail from Microsoft Outlook and will need to use the Microsoft Exchange utility MDB Viewer to see and delete these messages. For additional information on how to correct this problem, click the article numbers below to view the articles in the Microsoft Knowledge Base: Q9: Why does SQL Mail not delete my e-mail attachments?

A9: If you run the xp_sendmail stored procedure with a query and @attach_results is true, temporary files created for the attachments are left in the System32 directory in which the Windows operating system is installed. This behavior occurs with all versions of SQL Server. For additional information about this problem, click the article number below to view the article in the Microsoft Knowledge Base: Q10: Why are no mail profiles listed in the drop-down box in SQL Mail properties?

A10: One possible cause is that the service that SQL Server runs under is configured to start with the Local System account. The Local System account does not have a Windows login associated with it; therefore, no mail profiles can be configured for SQL Server. Refer to the Check Your Configuration section in this article for instructions on choosing the proper service startup account.

Another possible cause is that there is no mail profile for the account used to start the MSSQL Server service. To configure a mail profile to use with SQL Server Mail, you must first log on to the Windows computer on which SQL Server is running with the same login and password used to start the MSSQL Server service.

You may not have Microsoft Outlook set as the default e-mail client. Refer to the Check Your Configuration section in this article for instructions on examining your default e-mail program.
Q11: Why does SQL Mail only work when I am logged into the server?

A11: This suggests that you do not have SQL Mail properly configured. Follow the setup checklist in the Check Your Configuration section of this article to verify that you have SQL Mail configured correctly. For example, a working POP3 (incoming mail) server may not have been supplied to the mail configuration.

Another possible reason is that you are using Outlook 2002 with an Internet Mail server. Outlook 2002 treats non-Exchange servers differently than Exchange servers. Because the mail spooler is run from within the Outlook process space for non-Exchange mail servers, you must have the Outlook 2002 client running in order to send Internet mail. When you log off of the server, the Outlook client shuts down and mail is no longer sent. To fix this problem, you must either use Outlook 2000 or switch to an Exchange server.

Q12: Why does SQL Mail stop responding (hang)?

A12: If SQL Mail hangs periodically, the most likely cause is the loss of a connection to the mail server. Experience shows that SQL Mail is more likely to stop responding when you use an Internet mail (POP3/SMTP) server, but it can also stop responding if a connection to an Exchange server fails.

What happens is that the mail client attempts to connect to the mail server. If the connection fails, a message from the mail client appears that requests user action as to whether to continue or cancel. The message does not appear on the server because SQL Mail is run from the SQL Server service. Because a message does not appear, no user action can occur and SQL Mail waits indefinitely. Even if you re-establish the network connection, SQL Mail does not automatically reset and retry. You must stop and restart the SQL Server service before you can begin sending messages again.

Another possible reason for SQL Mail to stop responding is a new Outlook security feature that warns when a non-Outlook application is sending e-mail. SQL Mail for SQL Server 6.5 and SQL Server 7.0 are such non-Outlook applications. If a security dialog box window appears when SQL Mail is sent, this has the same effect of making SQL Mail stop responding as if the connection to the mail server were lost.

Q13: How can I tell if dialog box messages are causing SQL Mail to stop responding?

A13: Because SQL Mail is run through the SQL Server service, any dialog box messages that cause SQL Mail to stop responding do not appear on your desktop. To determine if SQL Mail is causing dialog box messages, you must run SQL Server as a console application, not as a service. To do so, use these steps:

1. Log on to the Windows server with the same account used to start the SQL Server service.
2. Open a command prompt window. If you are using SQL Server 2000, you must navigate to the directory in which the executable files for your SQL Server instance are installed.
3. Start SQL Server as an application by executing this command:
   
   sqlservr -c
   
   NOTE: For SQL Server 2000, if you use a named instance, you must add -s with the instance name.
4. Connect to SQL Server with Query Analyzer, and then run the xp_sendmail extended stored procedure to test sending a mail message. If you are not using SQL Server 2000, you may need to run the xp_startmail extended stored procedure to start SQL Mail before you try to send mail.
5. If a dialog box appears, you must correct the problem before you can use SQL Mail.

Q14: Why have my scheduled jobs that use mail notification stopped working?

A14: SQLAgentMail can also stop responding when invoked from a scheduled job in SQL Agent. Refer to the response to question 13 for the most common reasons that cause SQL Mail to stop responding. For example, when SQLAgentMail pages an operator after the completion of a scheduled job, this is a synchronous call. If the connection to the mail server stops responding, for whatever reason, then the job scheduler that issued the command to send mail stops responding and no other scheduled jobs can run until the problem is corrected and the SQL Server Agent is stopped and restarted.

Q15: Why do I get a "Mapi logon failure" message?

A15: The "Mapi logon failure" message generally means that SQL Mail is unable to find a mail profile with which to start. Follow the setup checklist in the Check Your Configuration section of this article to make sure that you have SQL Mail properly configured.

Another reason is that your mail profile is corrupted or the name of the profile is greater than 32 characters. You may want to try deleting the mail profile and re-creating it with a shorter name to ensure that you have a working mail profile.

Q16: Why do I get a "General mapi failure" message?

A16: The "General mapi failure" message is usually caused by an improper SQL Mail configuration. Follow the setup checklist in the Check Your Configuration section of this article to make sure that you have SQL Mail properly configured.

Q17: Why do I see errors 1073759806 and 1073759778 in my SQL Server error log?

A17: If you start SQL Mail by using the xp_startmail extended stored procedure on SQL Server 2000, you will see the following error messages in your SQL Server error log:

These are informational (severity = 1) messages due to starting SQL Mail with xp_startmail. With SQL Server 2000, SQL Mail automatically starts when you run xp_sendmail, so use of xp_startmail is not necessary. You can ignore these messages.

Q18: Why am I getting mail error 0x80040111 (or similar hex number) when I try to send mail?

A18: The first step in resolving this problem is to determine the error message associated with the hex error number. MAPI does not convert error numbers to strings, so SQL Mail only returns the error number. For additional information on how to interpret the MAPI return code, click the article number below to view the article in the Microsoft Knowledge Base: When you look up the hex value 0x80040111, you see that this corresponds to the MAPI error message MAPI_E_LOGON_FAILED, which indicates that SQL Mail failed to logon with the mail profile provided. You can then take corrective action based on the error message.

Q19: Why does SQL Mail work but SQLAgentMail does not, or vice versa?

A19: One possibility is that the SQL Server and SQL Agent services are starting with different accounts. Change the startup account of the service that does not work with mail to start with the same account of the service that does work with mail. Stop, and then restart the service before testing that this change corrects the problem.

NOTE: If you are using SQL Mail with the Internet Mail Service, a Personal Store file (.pst) is used to store downloaded e-mail messages from the POP3 server. This .pst file is used by any service that uses this mail profile, and causes a conflict if SQL Mail and SQL Agent Mail both use the same mail profile. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: Another possibility is that the profiles are different. After the service accounts are the same, change the mail utility that is not working to use the same mail profile that is working. Stop, and then restart the service before testing that this change corrects the problem.

It is important to realize that SQL Mail for SQL Server 6.5 and SQL Server 7.0 uses simple MAPI while SQLAgentMail uses extended MAPI. Some mail clients only support simple MAPI and will work with SQL Mail, but not with SQLAgentMail. For additional information on mail clients supported for use with SQL Mail, click the article number below to view the article in the Microsoft Knowledge Base: The default mail client can also affect the operation of SQL Mail. Refer to the Check Your Configuration section for instructions on how to check and change your default mail program.

Q20: Why does the Test button tell me that it can start and stop SQL Mail with my profile, but I cannot start SQL Mail?

A20: The Test button in the SQL Mail (or SQLAgentMail) property dialog box only tests that you can use the provided mail profile to start SQL Mail. It does not guarantee that SQL Mail will use that profile. The reason for this is that in some cases, SQL Mail may ignore the provided mail profile and may still use the specified default mail client. For example, if the default mail client is Outlook Express, and Outlook Express is not configured on the server, SQL Mail may try to use it during startup, and then SQL Mail does not function properly.

It is important that the default mail client on the server be the same mail client as that used to configure the mail profile provided to SQL Mail. Refer to the Check Your Configuration section for instructions on how to check and change your default mail program.

Q21: Why does SQL Mail not failover on my cluster server?

A21: SQL Mail is not supported on a clustered SQL Server because the MAPI interface used by SQL Mail is not cluster-aware. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: Q22: Why does SQL Mail not work with a distribution list?

A22: SQL Mail with SQL Server 6.5 and SQL Server 7.0 allow you to send mail by using a distribution list. Use of a distribution list with SQL Mail for SQL Server 2000 only works in specific configurations. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Additional Resources

For additional information on troubleshooting SQL Mail problems, see the following Microsoft Web site: