Database Mail

  • Database Mail is the email component in SQL Server 2005 that replaces SQL Mail of SQL Server 7 and SQL Server 2000. It is much more reliable than SQL Mail, under SQL Server heavy load.
  • Database Mail is an SMTP client. This means that you do not need to install a MAPI server, such as Outlook, on the SQL Server as with previous version of MSSQL. Database Mail talks directly to the mail server, while SQL Mail is a MAPI client that needed Outlook to actually send the emails.

Applies to:

  • Microsoft SQL Server 2005

Enabling Database Mail

  1. USE master;
  2. go
  3. SP_CONFIGURE 'show advanced options', 1;
  4. go
  5. RECONFIGURE;
  6. go
  7. SP_CONFIGURE 'Database Mail XPs',1;
  8. go
  9. RECONFIGURE;
  10. go
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
  1. You first need to enable the advanced options, on line 3 before enabling Database Mail XPs.
  2. You always need to run the reconfigure, on line 5. If you do not run the reconfigure command, the previous command will not be enabled.
  3. You also need to enable the broker.

Enabling the broker

  1. ALTER DATABASE msdb
  2. SET enable_broker;
  3. go
  • There is no response from Microsoft SQL Server.
  • To verify that you have enabled the broker:
  1. SELECT SUBSTRING(name,1,15) AS 'Database', is_broker_enabled
  2. FROM sys.databases
  3. WHERE name = 'msdb';
  4. go
Database        is_broker_enabled
--------------- -----------------
msdb            1

(1 row(s) affected)