Share via

  • Article

sp_processmail (Transact-SQL)

Uses extended stored procedures (xp_findnextmsg, xp_readmail, and xp_deletemail) to process incoming mail messages from the inbox for Microsoft SQL Server. Each message is expected to contain a single query in the message body. This procedure uses the xp_sendmail extended stored procedure to return the result set as an attachment to the message sender.

Important

Avoid using this procedure. The sp_processmail procedure processes information sent to you by mail from unknown sources and can be used to introduce malicious code into your environment. You must take great care to validate the code before executing it. This procedure is included for backward compatibility, but is not installed until you enable SQL Mail.

Note

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Topic link iconTransact-SQL Syntax Conventions

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 query mails for SQL Server to process. 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 their message bodies contain queries.

  • [ @filetype = ] 'filetype'
    Is the file extension to be used when the query result set is sent back to the message sender as an attachment. 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 varchar(132), with a default of guest.

  • [ @dbuse = ] 'dbname'
    Is the database context in which the query should be run. dbname is varchar(132), 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. sp_processmail only handles unread messages. 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 the specified file-type extension, 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 Folder Options from the Tools menu. On the File Types tab, in the Folder Options dialog box, specify the application to use to open the file.

Different sp_processmail jobs can be set up for queries in different databases. For example, you could adopt the convention that queries to the AdventureWorks database must have a subject of "SQL:AdventureWorks." Then, you could run sp_processmail specifying a subject of* SQL:AdventureWorks and a dbname of AdventureWorks. Different database queries and groupings can have other formatting structures. For example, distribution tasks can have a subject of SQL:distribution and a dbname of *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, modifying the Transact-SQL code, and creating a new procedure with customized behavior. Possible changes include:

  • Process only certain custom message types using the @type parameter with the xp_findnextmsg 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, and removing the attachments parameter.

  • Set the security context to run the query in a user context based on the message sender. If the e-mail user names are the same as your SQL Server user names, this is as simple as changing the call to xp_sendmail to use set_user = @originator. If your mail user names are not valid SQL Server user names (for example, if they contain embedded blanks), you could do a table lookup or character substitution to get the appropriate SQL Server user name to pass to xp_sendmail.

Permissions

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

Examples

The following example processes all messages in the inbox as queries in the AdventureWorks database. Result sets are returned to the client in CSV (comma-separated values) format.

EXEC sp_processmail
    @filetype = 'CSV'
    ,@separator = ','
    ,@dbuse = 'AdventureWorks';