SQL Server 2000 provides built-in stored procedures and extended stored procedures that you can use to process e-mail messages received in a predefined SQL Mail account mailbox or to send e-mail messages from within your own SQL code. The following sections describe the stored procedures (with their corresponding syntax) that can be run individually or executed within the instructions of another stored procedure.
You use the xp_startmail procedure to start a mail client session. This procedure should be executed first to ensure that a SQL Mail session is running. The syntax for xp_startmail is as follows:
xp_startmail [@user = 'username'], [@password = 'password']
'username' is optional; when it's specified, the SQL Server attempts to log on to the MAPI provider using this name.
'password' is optional; when specified, it's used as the password for the 'username'.
'username' and 'password' are both optional parameters defined with the sysname datatype and no default value. If these arguments are not provided, SQL Server will use the default mail profile associated with SQL Mail.
sysname is a SQL Server-supplied, user-defined datatype. It is used for table columns, variables, and stored procedure parameters that store object names.
Permission to execute xp_startmail defaults to members of the sysadmin fixed server role, but can be granted to any SQL Server user. Only one mail session can be active at a time; however, after a SQL Mail session is started, all users can use the same mail session. If an existing mail session is already active, xp_startmail will not start a new one and will return a message indicating that it is already running, as shown in the following example:
declare @rval int exec @rval = xp_startmail --'Outlook' select @rval as rval go SQL Mail session is already started. rval ----------- 1
You can use xp_stopmail to stop the current mail session.
You use the xp_stopmail procedure to stop the current mail client session. The syntax is simple:
Permission to execute xp_stopmail defaults to members of the sysadmin fixed server role, but can be granted to any SQL Server user.
The xp_sendmail stored procedure is used to send messages or query result sets to specified mail recipients. When invoking xp_sendmail, only the @recipients parameter is required; all other parameters are optional. To specify subsequent parameters, you will have to specify the parameters by name if you want to skip any. The full syntax for xp_sendmail is as follows:
xp_sendmail @recipients = 'recipients [;...n]' ,[@message = 'message'] ,[@query = 'query'] ,[@attachments = attachments] ,[@copy_recipients = 'copy_recipients [;...n]]' ,[@blind_copy_recipients = 'blind_copy_recipients [;...n]]' ,[@subject = 'subject'] ,[@type = type] ,[@attach_results = 'attach_value'] ,[@no_output = 'output_value'] ,[@no_header = 'header_value'] ,[@width = width] ,[@separator = separator] ,[@echo_error = 'echo_value'] ,[@set_user = user] ,[@dbuse = database]
Table 23.1 describes each element of the syntax.
|@recipients = 'recipients [;...n]'||This is the list of e-mail recipients separated by semicolons.|
|[@message = 'message']||This is the message to be sent, which can be up to 8000 bytes in size.|
|[@query = 'query']||This will send the specified T-SQL query result set, up to 8000 bytes. The result set of the query will be sent in the mail body. Because the query is being submitted by an external program and not the connection that issues the xp_sendmail command, bound connections are used to prevent blocking from occurring between the xp_sendmail DLL and the client that issued the xp_sendmail command.|
|[@attachments = attachments]||This specifies the filename for an attachment. The file, which can reside on a local or network share, must be accessible to the user account under which SQL Server is running.|
|[@copy_recipients ='copy_recipients[;...n]]'||This is the list of "copy to" recipients, separated by semi-colons. This is what would appear in the Cc: box of a mail message dialog box.|
|[@blind_copy_recipients = 'blind_copy_recipients [;...n]]'||This is a list of recipients, separated by semicolons, who are to receive a blind copy. This list would appear in the Bcc: section of a mail message dialog box.|
|[@subject = 'subject']||This is where you provide a subject line for the message.|
|[@type = type]||This is the input message type. The default is NULL. This is a fairly esoteric setting that for most uses is left NULL. For more information, investigate SQL Mail in the Windows NT Resource Kit.|
|[@attach_results = 'attach_value']||The default setting for this is False, which will cause a query result set to be included in the message body. A True value will cause the query result to be sent as an attachment.|
|[@no_output = 'output_value']||If set to True, this option will send the mail but not out-put anything to the client session. The default is False.|
|[@no_header = 'header_value']||If set to True, this option does not send the column header information with the query result set; it sends only the data rows. The default is False.|
|[@width = width]||This parameter is the same as the /w parameter in the ISQL utility. It indicates the output width for a query result set. The default is 80 characters?anything longer than 80 characters will wrap to the next line. Use a larger width to send a wide result set without line breaks inside the output lines.|
|[@separator = separator]||Here, you specify a configurable column separator for the result set. Tabs are the default separator, but using a comma here with the @attach_results option will produce a comma-delimited file.|
|[@echo_error = 'echo_value']||Setting this option to True will cause server messages and d-Library errors to be appended to the message rather than written to the SQL Server error log. In addition, a count of the rows affected by the query is appended to the mail message. Setting this option to True causes the xp_sendmail stored procedure to always return a success status (0) if the mail is successfully sent, regardless of any errors encountered by the query.|
|[@set_user = user]||This is the security context for the query. This allows a query to be run under a different security context from the session initiating the xp_sendmail command. The default user context is that of the user executing xp_sendmail.|
|[@dbuse = database]||This is the database name from which the query should be run. If it is not specified, the database context will be the user's default database.|
Only one user at a time can send an e-mail through the active SQL Mail session. Other users who are sending mail messages at the same time will wait in a queue until the previous user's mail message has been sent.
Permission to execute xp_sendmail defaults to members of the sysadmin fixed server role, but can be granted to any SQL Server user.
The following example sends an e-mail message to the SQL Operators distribution list with the output from sp_who2 as an attachment, setting the width of the query output to 255 bytes:
exec master..xp_sendmail 'SQL Operators', 'The attached file shows all current user activity on the server', 'exec sp_who2', @subject = 'Current User Activity', @width = 255, @attach_results = 'True'
While the SQL Mail stored procedures can be used to provide useful e-mail capabilities within your database applications and SQL code (for example, xp_sendmail can be used to automatically send the results of a report to a recipient list), a new feature is available for SQL Server 2000 to provide a means of forwarding messages and information to end users. This feature is called SQL Server Notification Services. SQL Server Notification Services is a platform for the development and deployment of notification applications. Notification applications send messages to users based upon subscriptions made to the notification application. Depending on how the subscriptions are configured, messages can be sent to the subscriber immediately or on a predetermined schedule.
For more information on setting up and using SQL Server Notification Services, see Chapter 45, "SQL Server Notification Services."
The xp_findnextmsg procedure is used to find the next message to be read from the mail profile's Inbox. It returns the message ID as an output parameter or a result row. The syntax is as follows:
xp_findnextmsg [@msg_id = 'message_number' [OUTPUT]] ,[@type = type] ,[@unread_only = 'unread_value']
The following paragraphs describe the elements of the syntax:
[@msg_id = 'message_number' [OUTPUT]]
This is the number of the message, returned as a varchar(255). When the OUTPUT clause is used, the message number is placed in this parameter. Otherwise, it is returned as a result set.
[@type = type]
This is the MAPI mail type. The default is NULL.
[@unread_only = 'unread_value']
When this is set to True, only unread messages are processed by the stored procedure. The default is False.
The following is an example that returns the message ID of the next unread message in the Inbox:
exec xp_findnextmsg @unread_only = 'true' go Message ID ------------------------------------------------------------------------------ 0xE4D12200
The xp_readmail procedure reads a mail message from the SQL Server Inbox. You can use xp_readmail to return the contents of the Inbox as a result set to the client or to read a single message from the Inbox. The syntax for xp_readmail is as follows:
xp_readmail [@msg_id = 'message_number'] ,[ @type = 'type' [OUTPUT]] ,[@peek = 'peek'] ,[@suppress_attach = 'suppress_attach'] ,[@originator = 'sender' OUTPUT] ,[@subject = 'subject' OUTPUT] ,[@message = 'message' OUTPUT] ,[@recipients = 'recipients [;...n]' OUTPUT] ,[@cc_list = 'copy_recipients [;...n]' OUTPUT] ,[@bcc_list = 'blind_copy_recipients [;...n]' OUTPUT] ,[@date_received = 'date' OUTPUT] ,[@unread = 'unread_value' OUTPUT] ,[@attachments = 'attachments [;...n]' OUTPUT] ,[@skip_bytes = bytes_to_skip OUTPUT] ,[@msg_length = length_in_bytes OUTPUT] ,[@originator_address = 'sender_address' OUTPUT]
When the OUTPUT keyword is specified for one or more of the xp_readmail parameters, the resulting value is returned to this parameter.
Table 23.2 describe each element of the syntax:
|[@msg_id = 'message_number']||This is a varchar (255) string indicating the message number to be read.|
|[ @type = 'type' [OUTPUT]]||This is a message type; the default is NULL. This is a fairly esoteric setting that for most uses is left NULL. For more information, investigate SQL Mail in the Windows NT Resource Kit.|
|[@peek = 'peek']||This parameter, when set to False, causes the message to be marked as read in the mail Inbox. A True value causes the message to remain marked as unread.|
|[@suppress_attach = 'suppress_attach']||If this parameter is set to True, message attachments are suppressed, and no temporary files are created.|
|[@originator = 'sender' OUTPUT]||This returns the mail address of the message sender as a varchar(255).|
|[@subject = 'subject' OUTPUT]||This returns the subject of the message as a varchar(255).|
|[@message = 'message' OUTPUT]||This returns the actual text of the message as a text parameter.|
|[@recipients = 'recipients [;...n]' OUTPUT]||This returns a list of recipients separated by semi-colons as a varchar(255).|
|[@cc_list = 'copy_recipients [;...n]' OUTPUT]||This returns a list of copied recipients separated by semicolons as a varchar(255).|
|[@bcc_list = 'blind_copy_recipients[;...n]' OUTPUT]||This returns a list of blind-copied recipients separated by semicolons as a varchar(255).|
|[@date_received = 'date' OUTPUT]||This returns the date the mail message was received.|
|[@unread = 'unread_value' OUTPUT]||This returns True if the message was previously unread.|
|[@attachments = 'attachments [;...n]' OUTPUT]||This returns a semicolon-separated list of the temporary paths of the attachments as a varchar(255).|
|[@skip_bytes = bytes_to_skip OUTPUT]||This parameter indicates the number of bytes to skip before reading the next 255 bytes of a message. It returns the next starting point within the message as an int.|
|[@msg_length = length_in_bytes OUTPUT]||This returns the total length, in bytes, of the message. Using this parameter with the bytes_to_skip output parameter in a stored procedure allows a message to be read in chunks of 255 bytes.|
|[@originator_address = 'sender_address' OUTPUT]||This returns the resolved mail address of the message sender as a varchar(255).|
To return the entire contents of the Inbox as a result set to the client, either set message_number to NULL or do not include the message_number parameter. If you want to limit the messages returned, the @msg_id can be used to read specific messages. You can also specify the @peek and @suppress_attach as input parameters to control how the message is read.
If using xp_readmail without specifying @msg_id to return the Inbox contents, xp_readmail returns a result set containing the mail messages (older messages appear first) with the columns, as shown in Table 23.3.
|Originator||The sender of the e-mail message|
|Date Received||The date the e-mail message was received|
|Recipients||The people to whom the message was sent|
|Cc List||The people on the Cc line of the e-mail message|
|Bcc List||The people on the Bcc line of the e-mail message|
|Subject||The subject line of the e-mail message|
|Message||The message body (text)|
|Unread||The read/unread status of the message|
|Attachments||Any attachments for the message|
|Message ID||The message ID|
|Type||The message type|
To read a single message from the Inbox, you need to supply a valid message number as an input parameter to xp_readmail. You can obtain the message number using the xp_findnextmsg procedure. When reading a single message, you can use the output parameters to return the specified information about the message into local variables, as shown in the following example:
declare @msg varchar(8000), @msgid varchar(255), @subject varchar(255), @sender varchar(255) exec xp_findnextmsg @unread_only = 'true', @msg_id = @msgid output if @msgid is NOT NULL -- make sure we have a message to read begin exec xp_readmail @msgid, @peek = 'True', @subject = @subject output, @originator = @sender output, @message = @msg output print 'Subject: ' + @subject print 'Sent By: ' + @sender print '------------------------------' print 'Message: ' + @msg end go Subject: Microsoft SQL Server Alert System Test Sent By: Ray Rankins ------------------------------ Message: Microsoft SQLServerAgent test notification - please ignore.
The xp_deletemail procedure deletes the specified message from the SQL Server Inbox. The syntax is as follows:
'message_number' is the number, specified as varchar(255), of the message that is to be deleted.
The sp_processmail procedure invokes the extended-stored procedures xp_findnextmsg, xp_readmail, and xp_deletemail to read and process incoming mail messages that are expected to contain a SQL query. The messages are read from the Inbox of the mail profile configured for SQL Server. Any result sets generated by the queries processed are sent back to the message sender using xp_sendmail. Typically, you will set up sp_processmail in a regularly scheduled job to periodically check for mail received in the SQL Mail Inbox.
The syntax is as follows:
sp_processmail [@subject = 'subject'], [@filetype = 'filetype'], [@separator = 'separator'], [@set_user = 'user'], [@dbuse = 'dbname']
Table 23.4 describes the parameters for the sp_processmail procedure.
|[@subject =] 'subject'||If you enter a subject line in this parameter, only messages with that subject line are processed. The default value of NULL indicates that all messages should be processed.|
|[@filetype = 'filetype']||This is the file extension, as a varchar(3) parameter, that will be used for the file attachment sent back containing the result set. The default is txt.|
|[@separator = 'separator']||This is the column separator to be used between each column in the result set. This is a varchar(3) parameter. The default separator is the tab character.|
|[@set_user = 'user']||This is the user context under which the query will be run. If not specified, the query will run in the current user context.|
|[@dbuse = 'dbname']||This specifies the name of the database where the query should be run. If not specified, the query will run in the current database context.|
The following example uses one session to send an e-mail containing a query to the e-mail account used by SQL Mail, and the next session uses sp_processmail to read the e-mail, execute the query, and return the result set as an e-mail attachment to the originating e-mail account:
xp_sendmail 'SQLAdmin', 'exec sp_who2', @subject = 'Run This Query' go exec sp_processmail @subject = 'Run This Query' go Mail sent. Queries processed: 1.