Database Mail in SQL Server 2005

The SQL Mail problems, that we faced in SQL Server 7.0 and 2000, are no more. SQL Server 2005 supports and uses SMTP email now and there is no longer a need to MAPI client to send email. In SQL Server 2005, the mail feature is called Database Mail. In this article, I am going to demonstrate step-by-step, with illustrations, how to configure Database Mail and send email from SQL Server.

Database Mail has four components.

1.     Configuration Component

Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.

The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members of DatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.

2.     Messaging Component

Messaging component is basically all of the objects related to sending email stored in the MSDB database.

3.     Database Mail Executable

Database Mail uses the DatabaseMail90.exe executable to send email.

4.     Logging and Auditing component

Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.

Step 1

Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.

In the SQL Server Management Studio, execute the following statement.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
  --sp_configure 'SQL Mail XPs',0
go
reconfigure 
go

Alternatively, you could use the SQL Server Surface area configuration. Refer Fig 1.0.

Fig 1.0

Step 2

The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this article, we are going create the account, "MyMailAccount," using mail.optonline.net as the mail server and

makclaire@optimumonline.net as the e-mail account.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyMailAccount',
      @description = 'Mail account for Database Mail',
    @email_address = 'makclaire@optonline.net',
      @display_name = 'MyAccount',
 @username='makclaire@optonline.net',
   @password='abc123',
    @mailserver_name = 'mail.optonline.net'
  

Step 3

The second sub component of the configuration requires us to create a Mail profile.

In this article, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'MyMailProfile',
         @description = 'Profile used for database mail'

Step 4

Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyMailProfile',
      @account_name = 'MyMailAccount',
    @sequence_number = 1
  

Step 5

Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.

Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MyMailProfile',
      @principal_name = 'public',
    @is_default = 1 ;
  

Step 6

Now let us send a test email from SQL Server.

Please execute the statement below.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
  EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
    @subject = 'My Mail Test',
      @body = @body1,
    @body_format = 'HTML' ;

You will get the message shown in Fig 1.1.

Fig 1.1

Moreover, in a few moments you will receive the email message shown in Fig 1.2.

Fig 1.2

You may get the error message below, if you haven't run the SQL statements from step 1.

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of
  component 'Database Mail XPs' because this component is turned off as part of
the security configuration for this server. A system administrator can enable
the use of 'Database Mail XPs' by using sp_configure. For more information
  about enabling 'Database Mail XPs', see "Surface Area Configuration"
in SQL Server Books Online. 

You may see this in the database mail log if port 25 is blocked. Refer Fig 1.3.

Fig 1.3

Please make sure port 25 is not blocked by a firewall or anti virus software etc. Refer Fig 1.4.

Fig 1.4

Step 7

You can check the configuration of the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail [Refer Fig 1.5] and clicking the Configuration. [Refer Fig 1.6]

Fig 1.5

Fig 1.6

Step 8

The log related to Database Mail can be viewed by executing the statement below. Refer Fig 1.7.

SELECT * FROM msdb.dbo.sysmail_event_log

Fig 1.7

Conclusion

This article has demonstrated step-by-step instructions, with illustrations, how to configure Database Mail and send email from SQL Server.