Using SQL Mail Stored Procedures

Administering SQL Server

Administering SQL Server

Using SQL Mail Stored Procedures

SQL Mail contains a number of stored procedures, which allow you to develop triggers, applications, and other stored procedures. The stored procedures can then be used to manipulate mail, run queries, return a result set to a list of recipients, or reply to an e-mail containing a simple query or stored procedure.

The following table provides a brief description of the extended procedures and how they can be used.

SQL Mail procedures Function
xp_startmail Starts a mail client session. The mail client session must be started prior to using any of the other mail stored procedures.
xp_stopmail Closes a Microsoft® SQL Server™ mail client session.
xp_findnextmsg Used with sp_processmail in order to process mail in the SQL Mail inbox by accepting a message ID for input and returning the message ID for output.
xp_readmail Used by sp_processmail to read a mail message from the SQL Mail inbox.
xp_deletemail Used by sp_processmail to delete a message from the SQL Mail inbox.
xp_sendmail Used by sp_processmail or as part of a stored procedure or trigger. Can be used with alerts. Sends a message and a query result set attachment to the specified recipients.
sp_processmail Uses extended stored procedures (xp_findnextmessage, xp_readmail, and xp_deletemail) to process incoming mail messages (expected to be a single query only) and uses xp_sendmail to return the result set to the message sender. sp_processmail must be set up as a regularly scheduled job to check for mail received in the SQL Mail inbox.

To use SQL Mail