Frequently asked questions - SQL Server - SQL Mail (311231)

MORE INFORMATION

The questions that follow are questions frequently asked of Microsoft SQL Server support professionals concerning SQL Mail. The questions are not ranked in order of frequency, but similar questions are grouped together.


Q1: Why do I need to install a mail client on my SQL Server computer in order to use SQL Mail?

A1: SQL Mail is a Mail Application Programming Interface (MAPI) based application that requires the MAPI messaging subsystem in order to send mail. A MAPI subsystem is installed when you install a MAPI-based client. Microsoft Windows NT 4.0 provides a MAPI client named Windows Messaging; however, Microsoft Windows 2000 does not provide a MAPI client. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: The easiest way to provide the MAPI subsystem for SQL Mail to use is to install a MAPI-based mail client such as Microsoft Outlook. The mail client also provides a mechanism for you to configure a mail profile that you can then assign to SQL Mail. The mail profile provides SQL Mail with the information it needs to connect to the mail server to send and receive mail.

Q2: What mail clients can I use with SQL Mail?

A2: The mail clients you can use with SQL Mail depend on the version of SQL Server and the operating system. If you are using Windows NT 4.0, you can use the Windows Messaging client provided with Windows NT 4.0 with SQL Mail for SQL Server 6.5 and SQL Server 7.0. The Windows Messaging client only works with an Internet mail server and will not work with SQL Server 2000. For additional information about mail client options for SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft Outlook 98 works with SQL Server 6.5 and SQL Server 7.0. Outlook 98 works with SQL Server 2000 only if you have applied Microsoft SQL Server 2000 Service Pack 1. Outlook 98 works with either an Exchange server or an Internet mail server. You must have Outlook 98 installed with the Corporate or Workgroup option for it to work with SQL Mail. The MAPI subsystem installed in the Internet Mail Only (IMO) mode is a stub that provides minimal MAPI support for the basic functionality of Microsoft Outlook and will not work with SQL Mail.

Microsoft Outlook 2000 works with SQL Server 6.5, SQL Server 7.0 and SQL Server 2000. Outlook 2000 works with either an Exchange server or an Internet mail server. You must have Outlook 2000 installed with the Corporate or Workgroup option for it to work with SQL Mail. The MAPI subsystem installed in the IMO mode is a stub that provides minimal MAPI support for the basic functionality of Microsoft Outlook and will not work with SQL Mail. For additional information about the IMO option for Outlook, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft Outlook 2002, provided with Microsoft Office XP, works with SQL Server 2000. Security features built into Outlook 2002 and later versions of Outlook cause SQL Mail to stop responding with SQL Server 6.5 and SQL Server 7.0. For Outlook 2002 or later versions to work with SQL Mail and SQL Server 2000, you should have an Exchange server. We do not recommend that you use Outlook 2002 or later versions with SQL Mail and Simple Mail Transfer Protocol (SMTP) or Post Office Protocol 3 (POP3) Internet mail servers. See the "Outlook 2002 Client" section in the following article in the Microsoft Knowledge Base article:

Q3: Can I use Outlook Express with SQL Mail?

A3: Because Outlook Express installs a simple MAPI messaging subsystem and does not provide the mechanism for creating a mail profile, you cannot use it with SQL Mail.

Q4: How do I create a mail profile if I am running Microsoft Small Business Server?

A4: Small Business Server is a special case because your Exchange server is on the same computer as SQL Server. For additional information about how to use SQL Mail on Small Business Server, click the following article number to view the article in the Microsoft Knowledge Base: Q5: Can I use SQL Mail with mail servers like Lotus Notes or Novell Groupwise?

A5: You can use SQL Mail with third-party mail servers like Lotus Notes or Novell Groupwise; however, this configuration is only supported if the third-party mail server is configured as an Internet mail server.

While it may be possible to configure Microsoft Outlook client to work with third-party mail servers, the use of the e-mail clients provided with those mail servers is not supported for use with SQL Mail. These e-mail clients provide their own implementation of the MAPI messaging subsystem, and have not been tested for use with SQL Mail.

Q6: Do I need to have a POP3 server if SQL Mail will never receive mail?

A6: Unless you have set up SQL Mail with an Exchange server for inbound e-mail, you must have a working POP3 server even if mail will never be received on the server. The reason for this is that Microsoft Outlook first checks the POP3 incoming mail server before it sends outgoing e-mail. If there is no valid POP3 incoming mail server, outgoing e-mail could be delayed, never sent, or SQL Mail can stop responding (hang).

Q7: Can I use my Yahoo! or Hotmail account as my POP3 server?

A7: Web-based mail services like Yahoo! Mail and Hotmail may not be usable as a POP3 server. Use of such services for business purposes may also be in violation of their Use of Service agreements.

Use of a POP3 account operated by an Internet Service Provider (ISP) such as MSN is also not a good idea. If SQL Mail fails to connect to the POP3 server for any reason, SQL Mail stops responding and you must stop, and then restart SQL Server before you can send mail again. Mail servers outside of your control could be taken down at any time for maintenance or slowdowns on the Internet could render them inaccessible.

A better solution is to install a POP3 server on your SQL Server computer and configure it with a mail account so that you have a local POP3 server and a guaranteed connection. The POP3 and SMTP protocols are public domain and you can obtain mail servers that support these protocols through the Internet for installation at modest or no cost. Microsoft does not endorse one particular Internet mail server over others, nor does it support the installation or administration of an Internet mail server.

Q8: How can I use SQL Mail if I do not want to install Microsoft Outlook on my SQL Server computer?

A8: If you are running Windows NT 4.0, it may be possible for you to use Windows Messaging, depending on your version of SQL Server. Refer to the previous question on mail clients available for use with SQL Mail for limitations on the Windows Messaging client.

If you are running Windows 2000, you must have the Microsoft Outlook client installed on your SQL Server computer.

You can also send mail from a Transact-SQL batch without the use of SQL Mail, by using CDO for NT Server (CDONTS) or CDO for Windows 2000 (CDOSYS) and the SQL Server OLE automation stored procedures. For additional information on how to create mail procedures using CDO, click the following article number to view the article in the Microsoft Knowledge Base: Q9: If I have Microsoft Exchange, do I still need to use Outlook?

A9: Outlook provides a client-side MAPI that is not always suitable for a server-side application such as SQL Server. The server-side MAPI that comes with Exchange Server is preferable to the client-side MAPI that comes with Outlook. Unfortunately, installation of the Exchange MAPI does not provide you with the ability to create the mail profile required by SQL Mail. There are techniques that you can use to create a mail profile if you would like to use the Exchange server-side MAPI. For additional information on how to create a mail profile with the Exchange server-side MAPI, click the following article number to view the article in the Microsoft Knowledge Base: You do not need to install Exchange Server on your SQL Server computer in order to install the server-side MAPI. All you need to install is the Microsoft Exchange Administrator. You must also add the Exchange services in order to create your profile. For additional information on how to add the Exchange services, click the following article number to view the article in the Microsoft Knowledge Base: Q10: Can I send HTML mail with SQL Mail?

A10: You cannot use SQL Mail to send HTML-formatted mail with the current versions of SQL Server because of MAPI limitations. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: If you need to send HTML-formatted mail, you may want to consider writing stored procedures that use Collaboration Data Objects (CDO) to send mail. For additional information on how to how to create mail procedures using CDO, click the following article number to view the article in the Microsoft Knowledge Base: Q11: Why do I not have an option to start SQL Mail in SQL Server 2000?

A11: There is no option to manually start SQL Mail in SQL Server 2000 or to autostart SQL Mail when SQL Server starts. SQL Mail automatically starts the first time you send a SQL Mail message. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: Starting and stopping SQL Mail by using the xp_startmail and xp_stopmail extended stored procedures can lead to memory leaks and Microsoft does not recommend that you use those stored procedures unless you apply SQL Server 2000 Service Pack 2. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: Q12: Why do I need to use a domain account to start SQL Server?

A12: You only need to use a domain account to start SQL Server if you use Exchange Server. SQL Mail has to be able to connect to Exchange Server by using Windows authentication to avoid being prompted for user credentials. A domain account that can log on to both the local SQL Server computer and the remote Exchange Server computer is the best way to accomplish this.

If you are using an Internet mail server, then you must provide an account name and password for the POP3 server when you configure the mail profile. Because this is the only authentication required to log on to the POP3 server, you do not need to start the SQL Server service from a domain account. SMTP servers do not usually require an account and password.

You should not start SQL Server from the Local System account if you are planning to use SQL Mail because you cannot associate a mail profile with the Local System account.

Q13: Do I need to use the same profile for both SQL Mail and SQL Agent Mail?

A13: Mail profiles are associated with a particular Windows logon account. If you configure the Internet Mail service in your profile, a Personal Store file (.pst) to store downloaded mail messages from the POP3 server is created in a default location. This .pst file is used by any service that uses this mail profile, and it causes a conflict if SQL Mail and SQL Agent Mail both use the same mail profile.

If you configure your mail profile with the Exchange service, you generally do not use a .pst file since Exchange mail will be kept on the Exchange server. In this case, you could use the same mail profile for both SQL Mail and SQL Agent Mail. However, if you do create a .pst file, only one service using the mail profile can access the .pst file at any one time. If you are using a .pst file in your mail profile, you will want to use different mail profiles for SQL Mail and SQL Agent Mail. See q313969 (insert link to article) for more details.

NOTE: If you specify a location for the .pst file, do not use a network share. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: If you start the SQL Server and SQL Agent services with different service accounts, you must configure a mail profile for each account. To configure a mail profile for a service account, you must log on to the server by using that service account.

Q14: What account can I use if my Exchange server and SQL Server server are in separate domains and there is no trust between the two domains?

A14: If you cannot send a mail message to your Exchange server from the Outlook client on your SQL Server computer without being prompted to enter your login credentials, then SQL Mail will not work. Any attempt to send mail will cause SQL Mail to stop responding when the dialog box prompt for user credentials appears on the virtual desktop.

To workaround this behavior, treat the Exchange server as an Internet mail server. You can pass the login account and password information when you configure the mail profile so that SQL Mail will not be prompted for the login information when connecting to the Exchange server in the other domain.

You can also use this technique if the SQL Server server and Exchange server are separated by a firewall.

Q15: Does SQL Mail work with Microsoft Desktop Engine (MSDE)?

A15: SQL Mail is not supported on MSDE. You must upgrade to a non-MSDE edition of SQL Server in order to use SQL Mail.

Additional resources

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

Didn't see an answer to your question? Visit the Microsoft SQL Server Newsgroups at: Microsoft SQL Server Newsgroups

Comments about this or other Microsoft Knowledgbase articles? Drop us a note at SQLKB@Microsoft.com