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.

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.

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'