Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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 [email protected].
EXEC master.dbo.xp_sendmail
@recipients=N'[email protected]',
@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'[email protected]',
@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)