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

  1. Enable Database Mail if not already enabled. To enable Database Mail, use the Database Mail Configuration Wizard.

  2. 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).

  3. 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


































  1. 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


    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.


    Specifies the format of the e-mail message, one of TEXT or HTML.


    Specifies the importance for the e-mail message.


    Specifies the sensitivity for the e-mail message.


    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.


The following example uses SQL Mail to send a message to

EXEC master.dbo.xp_sendmail 
    @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
    @body=N'The master database is full.' ;

Notice that the procedure runs in the msdb database.

See Also


Database Mail

Other Resources

sp_send_dbmail (Transact-SQL)
xp_sendmail (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance