SQL Server and Mail Integration

Microsoft® SQL Server™ provides a set of extended stored procedures that allow SQL Server to operate as a workgroup post office for a MAPI-enabled e-mail system.

The computer running SQL Server must be set up as an e-mail client. SQL Server Enterprise Manager is used to assign an e-mail account and password to the SQL Server installation. The mail component of SQL Server can then be enabled to be started automatically when the SQLServerAgent service is started. Alternatively, the mail component can be started and stopped at will using either SQL Server Enterprise Manager, or the xp_startmail, xp_stopmail, and xp_sendmail stored procedures.

When the mail component of SQL Server is running, it can be used to:

• Send e-mail from Transact-SQL batches, scripts, stored procedures, and triggers using xp_send_mail. The e-mail can be:
  • Message strings.
  • The result set of a query.
  • A Transact-SQL statement or batch to execute.
  • A page for an electronic pager.
• Read e-mail using sp_processmail, or a combination of xp_findnextmessage, xp_readmail, and xp_deletemail. The messages sent to SQL Server typically contain a Transact-SQL statement or batch to be executed. The statement is executed and the result set is returned as a reply e-mail with an optional CC: list.

SQL Server events and alerts can be combined with SQL Mail functionality to build a system in which a server running SQL Server can e-mail or page the relevant administrators automatically if serious conditions arise.