Search
Home
Articles
Backup
Books
Certification
FAQ
Products
Replication
Scripts
Seminars
Training
TSQL
MSDN Fourms
Philippine SSUG
Fort Worth SSUG
Oklahoma City SSDG
Resume
MHS Enterprises
BlowFrog Software
FilAm Software
AcrylicAcetate.com
Bargain Humidors
Western Humidor

Configuring and Managing SQL Mail 1 2 3 4 5 6 7

Extended Stored Procedures for Mail Processing

If you’re building a system where you’ll be calling and using the SQL Mail abilities directly, you’ll be using the extended stored procedures for SQL mail. There are seven extended stored procedures that can be used to manage e-mail from within SQL Server and are found in the master database.

The stored procedures shown in the next table are available, each is described with its respective parameters following the table.

Stored Procedure Purpose
Xp_startmail Starts SQL Mail – use this procedure to manually start the mail process to send mail within a stored procedure or other routine. Once started, the mail process will be live system-wide, not just in the single routine calling the xp_startmail procedure.

If you’ve set up the mail process to start automatically when SQL Server starts, you won’t need to call this procedure. You set up the process to start automatically from the Support Services option in the Enterprise Manager.

Xp_stopmail Stops SQL Mail – xp_stopmail will cease mail operations system-wide. To once again send mail, you must re-start the service.

If you have SQL Mail set to start automatically when SQL Server starts, it will re-start when you start SQL Server. Until that time, the mail services will be unavailable.

Xp_sendmail Sends a message using the parameters (destination, source, SQL statement, etc.) that you indicate.
Xp_findnextmsg Locates the next message in the SQL Mail mailbox so you can further process the mail, letting you create a batch-oriented mail system.
Xp_readmail Reads a message in the SQL Mail mailbox, letting you access the headers and content of the mail message.
Xp_deletemail Deletes a message from the inbox.
Xp_proccessmail Executes xp_findnextmsg, xp_readmail, xp_deletemail, and xp_sendmail to process all mail messages in the SQL Mail mailbox

Using XP_StartMail

Xp_startmail is used to start the mail services in SQL Server if they have not already been started, either by another call to this procedure or by setting the automatic start option in the SQL Mail properties. The syntax for startmail is:

xp_startmail [[@user =] 'user'] [,[@password =] ,password']

The user name and password for the mailbox can be specified, but this is not necessary unless you did not specify a password for the profile that is being used when you set up the SQL Mail properties.

Using XP_StopMail

Xp_stopmail stops all mail processes on the server. This will shut down the connection to the mail server and subsequent calls to the mail extended stored procedures will fail until the service is restarted. Xp_stopmail is very simple, with no parameters:

xp_stopmail

 

Configuring and Managing SQL Mail 1 2 3 4 5 6 7

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.