Using SQL Mail Stored Procedures

SQL Mail contains a number of stored procedures that can be used to run queries, return a result set to a list of recipients, or reply to an e-mail message that contains a simple query or stored procedure.

Note

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.. To send mail from SQL Server 2005, use Database Mail.

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

SQL Mail procedures	Function
xp_startmail (Transact-SQL)	Starts SQL Mail. Notice that procedures that use SQL Mail will start SQL Mail when necessary.
xp_stopmail (Transact-SQL)	Stops SQL Mail.
xp_findnextmsg (Transact-SQL)	By default, finds the most recently delivered e-mail in the Inbox and returns the unique message ID. It is also used by sp_processmail to process mail in the SQL Mail Inbox: xp_findnextmsg accepts a message ID for input and returns the message ID of the next message for output.
xp_readmail (Transact-SQL)	Takes a message ID as input and returns message-related information as output, such as the subject of the e-mail. (You can use xp_findnextmsg to generate this ID.) It is also used by sp_processmail.
xp_deletemail (Transact-SQL)	Takes a message ID as input and removes the associated message from the Inbox. (You can use xp_findnextmsg to generate this ID.) It is also used by sp_processmail.
xp_sendmail (Transact-SQL)	Takes several required parameters (such as recipients, messages, subjects, and queries) so that e-mail can be created. Also used by sp_processmail or as part of a stored procedure or trigger. It sends a message and a query result set attachment to the specified recipients.
sp_processmail (Transact-SQL)	Uses several extended stored procedures (xp_findnextmsg, 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. It must be set up as a regularly scheduled job to check for mail received in the SQL Mail inbox.

Security Note:
For improved security, you should limit permissions for all SQL Mail stored procedures and extended stored procedures to members of the sysadmin fixed server role.

See Also

Concepts

Database Mail

Help and Information

Getting SQL Server 2005 Assistance