sp_processmail System Stored Procedure

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 SQL Server. It uses the xp_sendmail extended stored procedure to return the results set back to the message sender.

Syntax

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

where

@subject = subject

Specifies the subject line of mail messages to interpret as queries for SQL Server. When specified, sp_processmail will process only messages that have this subject. By default, SQL Server processes all mail messages as though they were queries.

@filetype = filetype

Specifies the file extension to be used when sending the results set file back to the message sender. The default is .TXT.

@separator = separator

Specifies the column separator (field terminator) for each column of the results set (this information is passed to the xp_sendmail extended stored procedure to return the results set back to the message sender). The default is 'TAB', which is a special case for the tab character to be used between columns.

@set_user = user

Specifies the security context in which the query should be run. The default is 'guest' (the guest user).

@dbuse = dbname

Specifies the database context in which the query should be run. The default is the master database.

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 the SQL Executive to schedule an sp_processmail task. This will process mail at the specified frequency and record an informational message with the number of queries processed in the task history.

Results are sent as an attached file. The complete filename sent consists of "SQL" followed by a random string of numbers and then the specified extension (@filetype), for example, SQL356.TXT.

Important To get an appropriate icon attached to the mail message, make sure that the file type is associated properly. Create a file association by using the Windows NT File Manager. To associate the appropriate application with the file from the File Manager File menu, choose Associate. For example, to return .CSV files to a client as Excel files, associate the .CSV extension with the Microsoft Excel program. Any existing associations (for example, .TXT files are associated with NOTEPAD.EXE) will be handled correctly. If the mail recipient has the same association, he or she can double-click on the icon (in the returned mail message) and automatically load the application.

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

If desired, different sp_processmail tasks 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 @dbuse = 'pubs'. Different database queries and groupings could have other formatting structures. For example, distribution tasks could have @subject = 'SQL:distribution' and @dbuse = 'distribution'. Any of these can be scheduled tasks with the SQL Executive. For more information on scheduling events, see the Microsoft SQL Server Administrator's Companion.

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 don't delete the message after processing, (issue the xp_readmail a second time with @peek set to 'false').
• Send the query results in the body of the e-mail message by calling the xp_sendmail extended stored procedure with @attach_results 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.

Example

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

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

Permission

Permission to use this procedure defaults to the system administrator.

See Also