E-Mail Functionality in SQL Server 2005

Working With E-Mails From Database Mail

sp_send_dbmail is the built-in stored procedure in the msdb database that can be used to send e-mails. The following is a simple example of using sp_send_dbmail.

To send mail, you must be a member of the DatabaseMailUserRole in the msdb database, and have access to at least one Database Mail profile.

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'dinesh',
     @recipients = 'anybody@anywhere.com',
     @body = 'Use new feature of Database mail. No MAPI client needed'
     @subject = 'New Feature of Database Mail';

Another important feature in this stored procedure is the ability to send query results. Let us try a real world example. Very recently, a client asked me to send a daily e-mail of the error log count. You can use @query and @attach_query_results_as_file parameters to satisfy above requirement:

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Error Logs',
     @recipients = 'DBA@customer.com',
     @query = 'SELECT COUNT(*) FROM crm.Admin.errorlogs
          WHERE DATEDIFF(dd, "2006-09-02", getdate()) =0',
     @subject = 'Error Log Count',
     @attach_query_result_as_file = 1 ;

This feature is available in SQL Mail, too. However, in SQL Mail you cannot send it an attachment, which is possible in Database Mail. In addition, there are also a several new parameters in sp_send_dbmail.

@body_format specifies the format of the e-mail message: TEXT or HTML.

@importance specifies the importance of the e-mail message.

@sensitivity specifies the sensitivity of the e-mail message.

Let us look at where this information is stored in SQL Server 2005. System views in the msdb database contain the e-mail messages and attachments sent from Database Mail and the status of each message.

Database Mail updates these tables when each message is processed. Query the sysmail_allitems, sysmail_sentitems, sysmail_unsentitems, and sysmail_faileditems views to check the delivery status of an individual e-mail message.

The sysmail_mailattachments view contains the e-mail attachments in Database Mail messages.

Converting SQL Mail to Database Mail

There are several changes required for changing over to Database Mail.

As specified before, sp_send_dbmail was used to send mail while in SQL mail xp_sendmail was used. So you will need to map both stored procedure parameters

(Source: SQL Server 2005 Documentation)