How to: Convert Stored Procedures from SQL Mail to Database Mail (Transact-SQL)

SQL Server 2008 Books Online (October 2009)

Database Mail lets you send e-mail from SQL Server. Use the stored procedure sp_send_dbmail to send e-mail. The arguments that this procedure requires are similar to the arguments for xp_sendmail. Therefore, converting a procedure that uses xp_sendmail to use sp_send_dbmail is straightforward.

The most important additional parameter for sp_send_dbmail is the optional mail profile to use to send the message. You create a Database Mail profile by using the Database Mail Configuration Wizard, or the Database Mail stored procedures. Only the msdb database can be a mail host database.

To convert a stored procedure from SQL Mail to Database Mail

Enable Database Mail if not already enabled. To enable Database Mail, use the Database Mail Configuration Wizard.
Create a Database Mail profile if you do not already have a profile created. For more information about how to create a Database Mail profile, see How to: Create Database Mail Private Profiles (Transact-SQL).
Replace the call to xp_sendmail with a call to sp_send_dbmail. Map the arguments from xp_sendmail to sp_send_dbmail as shown in the following table.

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

When you update your procedure, consider the following differences:

No MAPI message typing is available. Because Database Mail does not use Extended MAPI, the stored procedure cannot set the message type.
If the @query parameter is provided, any errors from the query are returned to the session that called sp_send_dbmail.
If the @query parameter is provided, the query runs as the current user. However, the query may contain any valid Transact-SQL, including EXECUTE AS statements.
Database Mail supports the following options that are not supported in SQL Mail:

sp_send_dbmail Argument	Description
@profile_name	Specifies the mail profile to use for the message. Database Mail supports multiple profiles and multiple accounts for each profile to provide improved reliability and scalability. The @profile may be omitted if there is a default profile for mail host database or for the user calling sp_send_dbmail.
@body_format	Specifies the format of the e-mail message, one of TEXT or HTML.
@importance	Specifies the importance for the e-mail message.
@sensitivity	Specifies the sensitivity for the e-mail message.
@query_attachment_filename	Specifies the file name to use when the results of a query are attached as a file. If you use the xp_sendmail @query parameter with @attach_results = TRUE, the @attachments parameter can specify only one file to attach to the mail message. sp_send_dbmail has both @file_attachments and @query_attachment_filename.

Example

The following example uses SQL Mail to send a message to danw@Adventure-Works.com.

EXEC master.dbo.xp_sendmail 
    @recipients=N'danw@Adventure-Works.com',
    @message=N'The master database is full.' ;

The example that follows uses Database Mail and the default profile for the current user to send the same message:

EXEC msdb.dbo.sp_send_dbmail
    @recipients=N'danw@Adventure-Works.com',
    @body=N'The master database is full.' ;

Notice that the procedure runs in the msdb database.

Concepts

Other Resources

Help and Information