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
|
xp_sendmail Argument |
sp_send_dbmail Argument |
|
@recipients |
@recipients |
|
@message |
@body |
|
@query |
@query |
|
@attachments |
@file_attachments |
|
@copy_recipients |
@copy_recipients |
|
@blind_copy_recipients |
@blind_copy_recipients |
|
@subject |
@subject |
|
@type |
N/A |
|
@attach_results |
@attach_query_result_as_file |
|
@no_output |
@exclude_query_output |
|
@no_header |
@query_result_header |
|
@width |
@query_result_width |
|
@separator |
@query_result_separator |
|
@echo_error |
N/A |
|
@set_user |
N/A |
|
@dbuse |
@execute_query_database |
(Source: SQL Server 2005 Documentation)