Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
How to use SQL Mail (Transact-SQL)

How to use SQL Mail (Transact-SQL)

SQL Server 2000

How to use SQL Mail (Transact-SQL)

  New Information - SQL Server 2000 SP3.

SQL Mail uses several extended stored procedures that are necessary for mail enabling. These extended stored procedures are included in a dynamic-link library, SQLMAP70.DLL, which is installed with Microsoft® SQL Server™ 2000.

For security reasons, you should limit permissions for all SQL Mail stored procedures and extended stored procedures to members of the sysadmin fixed server role.

To process e-mail messages manually

  1. In SQL Query Analyzer, start a SQL Server Mail client session by executing xp_startmail.

  2. To find the ID of the next unread message in the mail box, execute xp_findnextmsg.

  3. To read a message or attachment, execute xp_readmail (using a specific message ID), and use the output variable in a SELECT statement to display the message in the result pane.

  4. To delete a message, execute xp_deletemail (using a specific message ID).

    xp_deletemail deletes message, but does not delete any attachments. You must delete them manually. You can suppress the generation of attachments by setting the suppress_attach parameter for xp_readmail to TRUE. For more information about security concerns with attachments, see xp_readmail.

    xp_deletemail does not keep a log of deleted messages or users who deleted the messages. This may cause auditing problems in an environment where several users have permission to execute xp_deletemail. To minimize this problem, limit permissions for xp_deletemail to members of the sysadmin fixed server role.

  5. To send a message or a query result set to specified recipients, execute xp_sendmail (with the query in the message body).

  6. Stop the SQL Server Mail client session by executing xp_stopmail.

To process multiple e-mail messages as a scheduled job

  1. In SQL Query Analyzer, start a SQL Server Mail client session by executing xp_startmail.

  2. Execute sp_processmail to find, read, respond to, and delete multiple messages.

  3. Stop the SQL Server Mail client session by executing xp_stopmail.

See Also

sp_processmail

xp_sendmail

xp_deletemail

xp_startmail

xp_findnextmsg

xp_stopmail

xp_readmail

Show:
© 2015 Microsoft