SQL Mail

SQL Mail provides a way to receive e-mail messages generated by Microsoft® SQL Server™. Messages can be triggered to provide you with the status of a job or a warning caused by an alert. SQL Mail can include a result set in a reply to e-mail messages that contain queries. SQL Mail allows SQL Server to send and receive e-mail by establishing a client connection with a mail server.

SQL Server uses two services to handle mail. MSSQLServer processes mail for all of the mail stored procedures. SQLServerAgent does not use SQL Mail to send e-mail. Instead, SQLServerAgent uses its own mail capabilities that are configured and operated separately from SQL Mail.

The SQL Server Agent mail features will be referred to as SQLAgentMail to distinguish it from the SQL Mail features provided by MSSQLServer. SQL Mail establishes an extended MAPI connection with a mail host, while SQLAgentMail establishes an extended MAPI connection on its own. Both SQL Mail and SQLAgentMail can connect with Microsoft Exchange Server, Microsoft Windows NT® Mail, or a Post Office Protocol 3 (POP3) server.

SQL Mail requires a post office connection, a mail store (mailbox), a mail profile, and the Windows NT 4.0 or Microsoft Windows® 2000 domain user account used to log in to an instance of SQL Server. SQL Mail consists of a number of stored procedures, which are used by SQL Server to process e-mail messages that are received in the designated SQL Mail account mailbox or to reply to e-mail messages generated by the stored procedure xp_sendmail. Using SQL Mail extended stored procedures, messages can be sent from either a trigger or a stored procedure. SQL Mail stored procedures can manipulate data, process queries received by e-mail and return the result set by creating a reply e-mail.

Processing an E-mail Request Received by SQL Server

To process e-mail automatically, you must create a regularly scheduled job that uses the stored procedure, sp_processmail. sp_processmail checks your SQL Mail mail profile and then checks your mailbox for mail. sp_processmail uses xp_sendmail to execute query requests contained in the text of the e-mail and then returns the result set to the original sender and any additional recipients. For example, a supplier may be allowed to execute a stored procedure that produces current inventory levels for all materials supplied by the organization.

SQLAgentMail

SQLAgentMail can use its own domain account and mail profile that is different from the one set up for SQL Mail. With SQL Server, you can configure SQLAgentMail e-mail messages to be sent when:

• An alert is triggered.

  Alerts can be configured to send e-mail notification of specific events that occur without implementing SQL Mail. For example, alerts can be configured to notify an operator of a particular database event that may need immediate action.

  For more information about configuring alerts, see Defining Alerts.

• A scheduled task, such as a database backup or replication event, succeeds or fails.

E-mail messages can be sent to a list of recipients informing them of the status of scheduled jobs for possible user action. You can expand the capabilities of jobs to include sending a result set by e-mail to a list of recipients. For example, a monthly inventory report could send SQLAgentMail notification to the designated operators and the result set to the purchasing manager and supplier.