Dela via

  • Artikel

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

@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

  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

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