Using SQL Mail Stored Procedures
New Information - SQL Server 2000 SP3.
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|| |
|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.|
Security Note For maximum security, you should limit permissions for all SQL Mail stored procedures and extended stored procedures to members of the sysadmin fixed server role.
To use SQL Mail