Configuring SQL Mail

SQL Mail is the vehicle by which special stored procedures within SQL Server can interact with MAPI systems. For these procedures to run correctly, you must link a mail profile set up for SQL Mail with the SQL Mail service. This is done by opening the Support Services folder under the server in Enterprise Manager, right-clicking on the SQL Mail item, and choosing the Properties option. This brings up the SQL Mail Properties dialog box where you can choose the mail profile you want SQL Mail to use (see Figure 23.2). This list should include the mail profile setup for the SQL Server user account, as described in the previous section.

Figure 23.2. Choosing the mail profile for SQL Mail in the SQL Mail Properties dialog box.

Alternatively, you can set up the mail profile for SQL Mail on the Server Settings tab of the Server Properties dialog box, as shown in Figure 23.3.

Figure 23.3. Invoking the SQL Mail Properties dialog box from the Server Settings tab of the Server Properties dialog box.

After choosing the appropriate mail profile, it is helpful to click on the Test button to ensure that SQL Server can initiate a mail session using the supplied mail profile.

Autostarting SQL Mail

In previous versions of SQL Server, an option on the SQL Mail Service Configuration dialog box was available to autostart SQL Mail on SQL Server startup. That option has disappeared in SQL Server 2000 because SQL Mail will automatically start when you execute the xp_sendmail stored procedure. Use of the xp_sendmail stored procedure is covered later in this chapter in the "SQL Mail Stored Procedures" section.

If you have problems with SQL Mail not starting automatically, or you prefer to have SQL Mail start during SQL Server startup, you have to create a startup stored procedure that invokes the xp_startmail stored procedure. (For more information on creating startup procedures, see Chapter 28, "Creating and Managing Stored Procedures in SQL Server.") Listing 23.1 provides a sample startup procedure to start SQL Mail automatically.

Listing 23.1 Sample Startup Procedure to Start SQL Mail Automatically on SQL Server Startup

 use master go create proc sp_SSU_startmail as declare @rval int exec @rval = xp_startmail if @rval = 1    print 'Unable to start SQL Mail' else    print 'SQL Mail started' go -- set the procedure option to run at SQL Server startup exec sp_procoption sp_SSU_startmail, 'startup', 'true' 

NOTE

In versions of SQL Server 2000 prior to Service Pack 2, there were some SQL Mail issues related to MAPI profile handling that resulted in overall performance, thread safety, and memory leak issues. At times, the MAPI application would hang, sometimes requiring a reboot of the machine to clear the problem and allow SQLMail to resume sending messages. According to Microsoft Knowledge Base Article Q300414, these bugs have been identified and addressed in Service Pack 2.

If you are using a version of SQL Server 2000 prior to Service Pack 2, you can minimize some of the memory leaks by starting SQL Mail once using the xp_startmail procedure and leave it running for the life of the SQL Server process, rather than stopping and starting SQL Mail repeatedly. Stopping and starting SQL Mail causes the connect and disconnect code for MAPI to run repeatedly, increasing the chances of a memory leak and stability problems.

If you are unable to upgrade SQL Server 2000 to Service Pack 2, you might want to implement the startup procedure shown in Listing 23.1 as a work-around until you are able to apply the SP2 fix.