SQL Server provides the ability to send and receive e-mail by using SQL Mail. SQL Mail has stored procedures and extended stored procedures that can be used to manipulate data, process queries received by e-mail, and return the results. This functionality of SQL Mail can be used to automate various processes.

If you have a business that deals with inventory, you can have a process where e-mail is sent automatically to your vendor if the inventory falls below a certain point. You can have an e-mail sent automatically to employees reminding them to log their time every Friday or have a query send you the output every day for customers who are past due on the billing deadline. In other words, the use of SQL Mail is endless, and you can manipulate SQL Mail to make your processes more automated and efficient.

SQL Server has two services to handle mail: SQL Agent Mail and SQL Server Mail. SQL Agent Mail is used to send notifications if a scheduled task, such as a database backup or other maintenance task, succeeds or fails. SQL Agent Mail can also be used to send alerts, for example, in a case where the transaction log is full or if you receive specific events requiring immediate action or attention for a particular database. So SQL Agent Mail can be used to keep an eye on the condition of your database(s), maintenance tasks, and other jobs being run on your SQL Server.

SQL Mail on the other hand comes with its own set of stored procedures and extended store procedures that give it the power to run queries, manipulate the data, and send the result of the queries to various users. You can disable SQL Agent Mail and still use SQL Mail functionality because SQL Agent Mail uses its own mail capabilities that are configured and operated separately from SQL Mail.

Before you can start using SQL Mail, you need to configure it so it can reliably send and receive mail. Below are a few of the steps that you should check to see if SQL Mail is configured properly.

1.) Make sure that you can send and receive e-mail using your mail client. There are various mail clients available, but Outlook has been proven to work the best with SQL Mail.

2.) As there are lots of mail clients available, so there are mail servers. SQL Mail has proven to work reliably with Microsoft Exchange servers. On the Microsoft Exchange server, set up a mailbox for the account to be used by the MSSQLServer service. This account must be a domain account. For example,

Account: DOMAIN_USA\SQLServerAccount_ONE
Mailbox: SQL1

3.) On the SQL Server computer, log on to Windows Server by using the same user account that is to be used by the MSSQLServer service. In the preceding step it would be

DOMAIN1_USA\SQLServerAccount_One

4.) Next you need to configure a Mail profile. You can do this by going to Control Panel, clicking the Mail icon, and then adding a profile.

5.) Once SQL Mail has been configured and a profile established, you can go ahead and check the profile by going to Enterprise Manager, Support Services, then to SQL Mail, and clicking the test button. For a detailed step-by-step configuration of SQL Mail please visit http://support.microsoft.com/default.aspx?scid=kb;en-us;263556

Most of the issues related to SQL Server Mail are due to its incorrect configuration. Following are some quick steps you can perform to check the configuration.

Are you able to send and receive e-mail messages using your mail client?
Make sure that you are able to send and receive e-mail messages using your mail client. SQL Mail uses the mail client to send and receive e-mail messages. If the mail client is not working properly, then you would have to resolve this issue before going any further.

What is the startup account for SQL Server?
Make sure that the startup account for SQL Server is a domain account and that it has a mailbox on the Exchange server.

Is the login account for Windows and SQL Server the same?
Make sure that the login account for Windows and the startup account for SQL Mail are the same. 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.

What is the Mail profile name?
Make sure that the Mail profile name is not longer than 32 characters and does not have special characters (such as hyphens, pound signs, periods, and so on).

Did you check the default mail client?
To obtain this information, right-click on Internet Explorer, click on Properties, and then go to the Programs tab. The default e-mail client is the name that appears in the E-mail box. As mentioned earlier, Outlook is the recommended mail client.

Why am I getting the "MAPI logon failure" message?
The "MAPI logon failure" message generally means that SQL Mail is unable to find a mail profile that it can start with. Make sure that SQL Mail is properly configured. To view a list of extended MAPI error codes please visit http://support. .com/default.aspx?scid=kb;en-us;238119. Other reasons for a failure message could be 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 recreating it with a shorter name to ensure that you have a working mail profile.

Although SQL Mail is very robust, it does have some limitations, as mentioned here.

SQL Mail is not supported in the 64-bit release of SQL Server 2000. The following extended stored procedures and stored procedures are not supported in this release, as they are part of the SQL Mail functionality:

xp_startmail
xp_stopmail
xp_sendmail
xp_readmail
xp_deletemail
xp_findnextmsg
sp_processmail

However, SQL Agent Mail in SQL Server 2000 (64-bit) can be configured remotely by Enterprise Manager if your client remotely connects using SQL Server 2000 Service Pack 3.0 or later. To configure SQL Agent Mail, you must use Microsoft Outlook Express to use an existing mail account.

SQL Mail is not fully supported on a SQL Server cluster due to MAPI not being cluster-aware. For more details, please visit http://support.microsoft.com/default.aspx?scid=kb;en-us;298723.

© 2003 Microsoft