Database Mail in SQL Server 2005/2008

SQL Server 2005/2008 has a default Mail Engine in it, We can send email from SQL Server itself using the Database Mail. Database Mail is not active by default, in order to activate the Database Mail we need to follow Database Mail Configuration wizard or we need to execute sp_configure stored procedure with appropriate parameter.

Database Mail Configuration wizard

1. Connect to SQL Server
2. Go to Management, right click Database Mail and select Configure Database Mail
3. Select appropriate options and select apply and ok

Some of the options available are

1. Account Retry Attempts
2. Maximum File Size
3. Prohibited Extensions
4. Account Retry Delay

To use this wizard, you must be a member of the sysadmin. In order to send email from Database Mail, you must be a member of DatabaseMailUserRole in msdb database

TSQL to Configure Database Mail

The configuration settings for Database Mail can be changed using the Transact-SQL Stored Procedure sysmail_configure_sp

Below given is the syntax for sysmail_configure_sp method and some of the parameters that are used.

sysmail_configure_sp [ [ @parameter_name = ] 'parameter_name' ]

    [ , [ @parameter_value = ] 'parameter_value' ]

    [ , [ @description = ] 'description' ]

Arguments

[@parameter_name = ] ‘parameter_name’ -     The name of the parameter to change.

[@parameter_value = ] ‘parameter_value’  -     The new value of the parameter.

[@description = ] ‘description’  -    A description of the parameter.

Configuring Email Account and Email Profile for Database Mail

In order to send email using database mail, following steps need to be followed.

1. Add Email Account
2. Add Email Profile
3. Map profile with Account
4. Add Principle Profile

To add an email account we need to use the TSQL sp sysmail_add_account_sp as given in the below example

EXECUTE msdb.dbo.sysmail_add_account_sp 

     @account_name = 'Udhay', 

     @description = 'Udhay test mail.', 

     @email_address = 'uravi@towardsjob.com', 

     @display_name = 'Udhay', 

     @mailserver_name = 'smtp.towardsjob.com' 

email profile can be created using the TSQL sp sysmail_add_profile_sp as given below

EXECUTE msdb.dbo.sysmail_add_profile_sp 

     @profile_name = 'Udhay', 

     @description = 'Udhay Profile' 

Next step is to map the profile and account using the TSQL sp sysmail_add_profileaccount_sp

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 

   @profile_name = 'Udhay', 

   @account_name = 'Udhay', 

   @sequence_number = 1 

Finally we need to setup the principle profile using TSQL sp sysmail_add_principalprofile_sp. This is done in order to ensure which is an active profile

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 

     @profile_name = 'Udhay', 

     @principal_name = 'public', 

     @is_default = 1 ;

Sending Email using Database Mail

To send an email we need to use the TSQL sp sp_send_mail with Recipients, Subject and Body as parameter

EXECUTE msdb.dbo.sp_send_dbmail

   @recipients = 'rajesh@towardsjob.com',

   @Subject = 'Test Email from SQL Server Database Mail',

   @Body = 'This is a test email sent from Database Mail'

Remove Email Account and Email Profile

If the email account created or profile created is no longer useful you can remove those accounts easily using the procedure sysmail_delete_account_sp and sysmail_delete_profile_sp as given below

-- To remove Email account

EXECUTE msdb.dbo.sysmail_delete_account_sp

    @account_name = 'Udhay';

-- To remove Email Profile

EXECUTE msdb.dbo.sysmail_delete_profile_sp

     @profile_name = 'Udhay';

Conclusion

I hope with the help of this article you would have understood the basic configuration of Database Mail in SQL Server 2005/2008. This is an excellent feature supported by Microsoft SQL Server 2005/2008 for DBA and Programmers. Kindly drop down comments if you have any

Related Posts

1. SQL Server 2005 database DBA Checklist for database administrator
2. Troubleshooting SQL Server 2005 Jobs Failure
3. Encryption and Decryption in SQL Server 2005
4. SQL Server 2005 Components
5. An Introduction to Microsoft SQL Server 2008 – Part 1

Tags: Database Mail, Email from SQL Server Database, SQL Server 2005, SQL Server 2005 Email, SQL Server 2008, SQL Server 2008 Email