Article ID: 315886 - View products that this article applies to.
This article was previously published under Q315886
This article uses a question and answer format to identify common problems you may encounter when you work with SQL Mail and provides you with some tips on how to correct or troubleshoot these problems. The vast majority of SQL Mail related problems are due to an improper configuration. For additional information on how to correctly configure SQL Mail, click the article number below to view the article in the Microsoft Knowledge Base:
263556This article presumes a working knowledge of the background information provided in Q263556. Unless otherwise stated, comments in this article regarding SQL Mail also apply to SQLAgentMail.
(http://support.microsoft.com/kb/263556/EN-US/ )INF: How to Configure SQL Mail
Frequently Encountered ProblemsA 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:
200018If 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.
(http://support.microsoft.com/kb/200018/EN-US/ )INFO: Differences Between CDO, Simple MAPI, and Extended MAPI
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.
A program is trying to automatically send e-mail on your behalf. Do you want to allow this?
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:
262631Microsoft Outlook 2002 includes the security features found in the Outlook 2000 security update.
(http://support.microsoft.com/kb/262631/EN-US/ )OL2000: Information About the Outlook E-mail 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 18.104.22.16801 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:
(http://support.microsoft.com/kb/263297/EN-US/ )OL2000: Administrator Information About the Outlook E-mail Security Update
290499Q5: Why is my outgoing mail stuck in the Inbox?
(http://support.microsoft.com/kb/290499/EN-US/ )OL2002: Administrator Information About E-Mail Security Features
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:
237913These 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.
(http://support.microsoft.com/kb/237913/EN-US/ )INFO: Outlook Object Model Unsuitable to Run in Windows Service
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:
(http://support.microsoft.com/kb/261133/EN-US/ )FIX: DTS "Send Mail" Fills Up Mailbox and Outlook Cannot See or Delete E-Mail Messages
308758Q9: Why does SQL Mail not delete my e-mail attachments?
(http://support.microsoft.com/kb/308758/EN-US/ )FIX: SQL Agent Mail May Cause E-mail to Be Written to Root Directory of Source Mailbox
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:
151545Q10: Why are no mail profiles listed in the drop-down box in SQL Mail properties?
(http://support.microsoft.com/kb/151545/EN-US/ )BUG: xp_sendmail With @query Leaves Temporary Files on Server
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 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 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 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:
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 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 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.
Error: 1073759806, Severity: 1, State: 0 Starting SQL Mail session....
Error: 1073759778, Severity: 1, State: 0 SQL Mail session started..
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:
238119When 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.
(http://support.microsoft.com/kb/238119/EN-US/ )INFO: List of Extended MAPI Numeric Result Codes
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:
313969Another 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.
(http://support.microsoft.com/kb/313969/EN-US/ )PRB: The Error 'Cannot Open .pst File' Occurs When You Use a POP3 Server Profile with SQL Mail
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:
311231The default mail client can also affect the operation of SQL Mail. Refer to the section for instructions on how to check and change your default mail program.
(http://support.microsoft.com/kb/311231/EN-US/ )INF:Frequently Asked Questions - SQL Server - SQL Mail
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 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:
298723Q22: Why does SQL Mail not work with a distribution list?
(http://support.microsoft.com/kb/298723/EN-US/ )BUG: SQL Mail Not Fully Supported for Use in Conjunction with Cluster Virtual SQL Servers
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:
(http://support.microsoft.com/kb/315666/EN-US/ )BUG: XP_SENDMAIL Fails to Send Mail to Personal Distribution List
Contact us for more help
Connect with Answer Desk for expert help.