|
 |
About Us | |
|
SSWUG Articles | |
 |
Articles |
 |
Administering | |
|
Comparison | |
|
General | |
|
Know How | |
|
Replication | |
|
Tuning | |
|
Undocumented | |
|
UDF | |
|
SQL 6.5 |
|
FAQ | |
|
Scripts | |
|
Tips | |
|
Test Exams | |
|
Advertise | |
|
Download | |
|
History | |
|
Search | |
|
Traffic | |
|
Related Links | |
|
||
|
|
||
Troubleshooting SQL MailAlexander Chigrik If you have problems with SQL Mail, review this 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 what SQL Server service pack are you running, see this link: How can I check what SQL service pack am I running? 2. Check that 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 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 can 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 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 a 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 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: 1. Run SQL Server Enterprise Manager. 2. Expand a server group; then expand a server. 3. Expand the Support Services folder and select SQL Mail. 4. Right-click SQL Mail and click Properties. 5. 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 can 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 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 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 SQL Server 2000 bug. This bug was fixed in SQL Server 2000 service pack 2. |