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