If you have problems with SQL Mail, review this 20-point troubleshooting checklist
to find potential solutions.
1. Check that you use the latest SQL Server service pack.
Because many bugs were fixed in SQL Server service packs,
you should install the latest SQL Server service pack.
To check which SQL Server service pack are you running see
How can I check what SQL service pack am I running?
2. Check that the account the MSSQLServer services runs under is a member of
the Administrators local group and a member of the Domain Users group.
The LocalSystem account does not have network access rights, but
to send and receive e-mail you should have network write privileges.
Because SQL Mail uses MSSQLServer service to send and receive e-mail,
you should set the account the MSSQLServer service runs under to be
a member of the Administrators local group and a member of the Domain
Users group.
3. Check that the account the SQLServerAgent services runs under is
a member of the Domain Users group.
When you use notification through e-mail, you use SQLAgentMail instead
of SQL Mail. Because SQLAgentMail uses SQLServerAgent service to send
e-mail, you should set the account the SQLServerAgent service runs under
to be a member of the Domain Users group.
4. Before testing SQL Mail, check that you can send and receive
e-mail manually.
If SQL Mail cannot send or receive e-mail, it might be a Microsoft Exchange
Server, or Microsoft Outlook problem. So, you should check and test SQL
Mail only if you can send and receive e-mail manually.
5. Ensure that Microsoft Outlook has been installed with the Corporate
or Workgroup option, if you use the Microsoft Outlook e-mail client.
Do not install Microsoft Outlook with the Internet Only option because
the Internet Only option installs only a stub of the Mapi32.dll file
and this file version does not work with SQL Mail.
6. If SQL Mail connects with Microsoft Exchange Server, confirm that the
Exchange Server profile used does not have a Personal Message Store.
7. You can have problems if you use SQL Mail with clustering.
Because SQL Mail is not fully supportable when used on a SQL Server
Failover Cluster (due to the MAPI limitation of not being cluster-aware),
try to setup and configure SQL Mail on the server box without clustering.
8. If you are using Lotus Notes, Novell GroupWise or other third-party
mail servers, you must configure the mail server as a POP3 server.
Because Microsoft does not support connecting to Lotus Notes or Novell
GroupWise mail servers using their native mail services, you must configure
a third-party mail server as a POP3 server.
9. Ensure that the account the MSSQLServer services runs under is the
same account that was used to log on to Windows NT or Windows 2000
server, when you set up your mail profile, test the mail connection,
and then provide this mail profile to SQL Mail.
10. Verify that the SQL Mail profile is correct.
To verify that the SQL Mail profile is correct, you can do the following:
- Run SQL Server Enterprise Manager.
- Expand a server group; then expand a server.
- Expand the Support Services folder and select SQL Mail.
- Right-click SQL Mail and click Properties
- Choose Profile name and then click Test button.
11. Ensure that Microsoft Outlook Express is not set as the default
e-mail client.
If Microsoft Outlook Express is set as the default e-mail client, you
may get an error indicating that the profile was incorrect and SQL Mail
will not start.
12. Try to use Microsoft Exchange Server for sending and receiving
SQL Mail.
Microsoft recommends using Microsoft Exchange Server for sending and
receiving SQL Mail, because using this way provides best results.
Note. Keep in mind that Microsoft Windows NT Mail cannot be used with
SQL Mail 2000
13. When you specify a SQL Mail profile name, try not to include special
characters (such as hyphens, pound signs, periods, and so on).
These characters can be used in the profile name when you work with Exchange
clients (such as Outlook), but it is not recommended to use them in a
SQL Mail profile name.
14. When you specify a SQL Mail profile name, the name should not be
longer than 32 characters.
15. Try to install the latest version of a mail client.
Because many problems with configuring SQL Mail are due to use of an
incorrect Mapi32.dll file, you should install the latest version of
a mail client. For example, only the Microsoft Outlook 2000 client,
or later, is supported for use with SQL Mail 2000.
16. You can get the error 18007 when you attempt to run sp_processmail
to process queries sent through e-mail.
This is an SQL Server 7.0 bug; SQL Server 2000 does not contain such problems.
To work around this problem, change sp_process mail to declare @set_user
and @dbuse as varchar(128) instead of sysname.
17. The xp_sendmail extended stored procedure breaks text data into
4,096 byte pieces and each piece is separated with a carriage
return and a tab.
To work around this problem, you can send the text as an attachment
using the @attachments parameter of the xp_sendmail extended stored
procedure.
18. The xp_sendmail extended stored procedure causes an exception error
and stop if the amount of results in bytes of the query is greater
than 8 megabytes (MB) and the results are not attached as a file.
To work around this problem, you should use the @attach_results=true
parameter with xp_sendmail extended stored procedure when the results
are very large.
19. The xp_sendmail extended stored procedure may cause error 35909.
To work around this problem, you should use the @attach_results=true
parameter with xp_sendmail extended stored procedure or do not set
@width parameter greater than 2000.
20. The sp_processmail stored procedure may fail with error 18023.
This is an SQL Server 2000 bug. This bug was fixed in SQL Server 2000
service pack 2.
