How to: Convert Stored Procedures from SQL Mail to Database Mail (Transact-SQL)
Database Mail lets you send e-mail from Microsoft SQL Server 2005. 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, @query_attachment_filename |
@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_name 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.
See Also
Concepts
Other Resources
sp_send_dbmail (Transact-SQL)
xp_sendmail (Transact-SQL)