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

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

  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



    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.


    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



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.