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'