Configure SQL 2005 Database Mail by using stored procedures

Today I tried the new feature of sending email via SQL Server 2005 database mail which replaces the old SQL mail in SQL Server 2000. Of course, using SQL Server Management Studio is a convenient way to configure the database mail, but I would like to talk about how to use stored procedures to configure and send an email in SQL Server 2005.

The configuration components in SQL Server 2005 include two parts: one is Database Mail Account configuration; the other is Database Mail Profile configuration. SQL Server 2005 provides the following stored procedures in msdb database to manage the configurations:
sysmail_add_account_sp
sysmail_add_principalprofile_sp
sysmail_add_profile_sp
sysmail_add_profileaccount_sp
sysmail_configure_sp
sysmail_delete_account_sp
sysmail_delete_principalprofile_sp
sysmail_delete_profile_sp
sysmail_delete_profileaccount_sp
sysmail_update_account_sp
sysmail_update_principalprofile_sp
sysmail_update_profile_sp
sysmail_update_profileaccount_sp

Assume that you have an internet email account testemail@126.com, you would like to send an email from the email address in SQL 2005. Then you can refer to the steps as follows:
1. Configure your SQL Server instance to allow SQL Database Email.
sp_configure 'show advanced options',1
Go
reconfigure
Go
sp_configure 'Database mail XPs',1
Go

2. Add an email account.
execute msdb.dbo.sysmail_add_account_sp
@account_name='Test DBMail Account',
@description='Test mail account for Database mail',
@email_address = 'testemail@126.com',
@display_name='Test Database mail',
@mailserver_name = 'smtp.126.com',
@mailserver_type = 'SMTP',
@port = 25,
@username = 'testemail',
@password = 'testemail',
@use_default_credentials = 0,
@enable_ssl = 0
Go

3. Add an profile
execute msdb.dbo.sysmail_add_profile_sp
@profile_name='Test DBMail Profile',
@description = 'Profile for testing Database mail'
Go

4. Relate the account to the profile
execute msdb.dbo.sysmail_add_profileaccount_sp
@profile_name='Test DBMail Profile',
@account_name='Test DBMail Account',
@sequence_number = 1
Go

5. Add profile principal and set it as the default profile
execute msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test DBMail Profile',
@principal_name = 'public',
@is_default = 1
Go

After the above configurations, test sending an email via the following stored procedure:
execute msdb.dbo.sp_send_dbmail @profile_name = 'Test DBMail Profile',
@recipients = 'test@myemailserver.com',
@subject = 'Test Database Mail',
@body = 'Phenix Sun lost in the game 6. Nash cried to go back home.'
Go

To check if your email has been sent, you can run "select * from msdb.dbo.sysmail_mailitems" to check if the sent_status is 1. If your email fails to be sent, run "select * from msdb.dbo.sysmail_event_log" to check the error information.

As you can see, it is also very easy of configurations to send an email in SQL Server 2005.


-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------