sp_processmail (T-SQL)

Uses extended stored procedures (xp_findnextmsg, xp_readmail, and xp_deletemail) to process incoming mail messages (expected to be only a single query) from the inbox for Microsoft® SQL Server™. It uses the xp_sendmail extended stored procedure to return the result set to the message sender.

Syntax

sp_processmail [[@subject =] 'subject']
    [,[@filetype =] 'filetype']
    [,[@separator =] 'separator']
    [,[@set_user =] 'user']
    [,[@dbuse =] 'dbname']

Arguments

[@subject =] 'subject'

Is the subject line of mail messages to interpret as queries for SQL Server. subject is varchar(255), with a default of NULL. When specified, sp_processmail processes only messages that have this subject. By default, SQL Server processes all mail messages as though they were queries.

[@filetype =] 'filetype'

Is the file extension to be used when sending the result set file back to the message sender. filetype is varchar(3), with a default of txt.

[@separator =] 'separator'

Is the column separator (field terminator) for each column of the result set. This information is passed to the xp_sendmail extended stored procedure to return the result set to the message sender. separator is varchar(3), with a default of tab, which is a special case for the tab character to be used between columns.

[@set_user =] 'user'

Is the security context in which the query should be run. user is sysname, with a default of guest (the guest user).

[@dbuse =] 'dbname'

Is the database context in which the query should be run. dbname is sysname, with a default of master.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

Incoming e-mail is expected to have a single valid SQL Server query as the message text. The results of the query are returned to the message sender and copied to any e-mail users on the CC: list of the original message. After messages are processed, they are deleted from the inbox. If e-mail is often sent to the server, sp_processmail should be run frequently. To set up regular e-mail processing, you can use SQL Server Agent to schedule an sp_processmail job. This processes mail at the specified frequency and records an informational message with the number of queries processed in the job history.

Results are sent as an attached file. The complete file name sent consists of Sql followed by a random string of numbers and then the specified extension (file type), for example, Sql356.txt.

Important To attach an appropriate icon to the mail message, make sure the file type is associated properly. To create a file association, double-click My Computer on your desktop and select Options from the View menu. On the File Types tab, in the Options dialog box, specify the application to use to open the file.

Errors received when the query is processed are returned to the message sender through the message text. When the result set is returned to the client, xp_sendmail is called with the @echo_error parameter set to true. The messages sent also include a rowcount (number of rows affected) by the query.

Different sp_processmail jobs can be set up for queries in different databases. For example, you could adopt the convention that queries to the pubs database must have a subject of SQL:pubs. Then, you could run sp_processmail with subject = SQL:pubs and dbname = pubs. Different database queries and groupings can have other formatting structures. For example, distribution tasks can have subject = SQL:distribution and dbname = distribution. Any of these can be scheduled jobs with the SQL Server Agent.

The sp_processmail system stored procedure can also be customized in many ways by retrieving the text of the procedure with the sp_helptext system stored procedure and then modifying the Transact-SQL code. Possible changes include:

• Process only certain custom message types using the @type parameter with the xp_readmail extended stored procedure.
• Mark the message as read but do not delete the message after processing (execute xp_readmail a second time with peek set to false).
• Send the query results in the body of the e-mail message by calling xp_sendmail with attach_result set to false.
• Set the security context to run the query in a user context based on the message sender. If the e-mail usernames are the same as your SQL Server usernames, this is as simple as changing the call to xp_sendmail to use set_user = @originator. If your mail usernames are not valid SQL Server usernames (if, for example, they contain embedded blanks), you could do a table lookup or character substitution to get the appropriate SQL Server username to pass to xp_sendmail.

Permissions

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute this procedure.

Examples

This example processes all messages in the pubs database with result sets returned to the client in CSV (comma separated values) format.

sp_processmail @filetype = 'CSV', @separator = ',', @dbuse = 'pubs'

See Also