xp_sendmail Extended Stored Procedure

Sends a message, and/or a query results set, and/or an attachment to the specified recipients.

Syntax

xp_sendmail @recipient = recipient [; recipient2; [...; recipientn]]
    [, @message = message]
    [, @query = query]
    [, @attachments = attachments]
    [, @copy_recipients = recipient [; recipient2; [...; recipientn]]]
    [, @blind_copy_recipients = recipient [; recipient2; [...; recipientn]]]
    [, @subject = subject]
    [, @type = type]
    [, @attach_results = {'true' | 'false'}]
    [, @no_output = {'true' | 'false'}]
    [, @no_header = {'true' | 'false'}]
    [, @width = width]
    [, @separator = separator]
    [, @echo_error = {'true' | 'false'}]
    [, @set_user = user]
    [, @dbuse = dbname]

where

@recipient = recipient

Is a required parameter specifying the people to whom you are sending the mail. If you specify more than one name, separate the names with semicolons.

@message = message

Is an optional parameter that specifies the message to be sent.

@query = query

Is an optional parameter that specifies a valid SQL Server query, the result of which will be sent in mail.

@attachments = attachments

Is an optional parameter that specifies a file to attach to the mail.

@copy_recipients

Is an optional parameter that identifies recipients to whom you are sending a copy of the mail (cc:'ing).

@blind_copy_recipients

Is an optional parameter that identifies recipients to whom you are sending a blind copy of the mail (bcc:'ing).

@subject = subject

Is an optional parameter that specifies the subject of the mail. If you do not specify a subject, "SQL Server Message" is used as the subject.

@type = type

Is the input message type based on the MAPI mail definition:

IP[M | C].Vendorname.subclass

Message types beginning with IPM will appear in the inbox of the mail client, and will be found or read by xp_findnextmsg if the @type is NULL. Message types beginning with IPC do not appear in the inbox of the mail client and must be found or read by setting the @type parameter. The default is NULL.

For details about using custom message types, see the Microsoft Windows NT Resource Kit or the Microsoft Mail Technical Reference, available separately.

@attach_results = {'true' | 'false'}

Is an optional parameter that specifies that the results set of a query should be sent in mail as an attached file instead of being appended to the mail. If @attachments is not NULL and @attach_results is true, the first filename in @attachments will be used as the filename for the results. If @attachments is NULL, a filename will be generated with a .TXT extension. The default for this parameter is false, which means that the results set is appended to the message.

@no_output = {'true' | 'false'}

Is an optional parameter that sends the mail but does not return any output to the client session that sent the mail. The default is false, which means that the client session of SQL Server receives output.

@no_header = {'true' | 'false'}

Is an optional parameter that sends the query results in mail but does not send column header information with the query results. The default is false, which means that column header information is sent with the query results.

@width = width

Is an optional parameter that sets the line width of the output text for an @query message. This parameter is identical to the /w parameter in isql. For queries that produce long output rows, use @width together with @attach_results to send the output without line breaks in the middle of output lines. The default width is 80 characters.

@separator = separator

Specifies the column separator (field terminator) for each column of the results set. This allows the files to be accessible by spreadsheet applications for formatting, and so on. This parameter is identical to the /t parameter in isql. For example, use @separator with @attach_results to send files with comma-separated values.

@echo_error = {'true' | 'false'}

When true, causes SQLMail to capture any server messages or DB-Library errors encountered while running the @query and append them to the mail message rather than writing them to the error log. Also, a count of rows returned/rows affected will be appended to the mail message.

Note If @echo_error is true, xp_sendmail returns a status of 0 (success) if the mail is successfully sent, even if DB-Library errors or messages are encountered or the query returns no results.

@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 NULL (which means the user is placed in his or her default database).

Remarks

The SQLMail session must be started prior to executing xp_sendmail. Sessions can be started either automatically (using the Auto Start Mail Client option in the Set Server Options dialog box of setup) or with xp_startmail. One SQLMail session supports all users on the SQL Server, but only one user at a time can send a message. Other users sending mail messages will automatically wait their turns until the first user's message is sent.

If @query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQLMail makes a separate connection to SQL Server; it does not share the same connection as the original client connection that issued xp_sendmail. Note that @query can be blocked by a lock held by the client connection that issued xp_sendmail. For example, if you are updating a table within a transaction and you create a trigger for that update that attempts to select the updated row information as the @query parameter, the SQLMail connection will be blocked by the exclusive lock held on that row by the initial client connection.

For details, see the Microsoft SQL Server Administrator's Companion.

Examples

A.    With No Variables

This example sends a message to user1 that the master database is full.

xp_sendmail 'user1', 'The master database is full.'

B.    With Variables

This example sends the message to user1 and user2, with copies sent to user3 and user4. It also specifies a subject line for the message.

xp_sendmail @recipients = 'user1;user2', 
@message = 'The master database is full.',
@copy_recipients = 'user3;user4',
@subject = 'Master Database Status'

C.    Send Results

This example sends the results of the sp_configure system stored procedure to user1.

xp_sendmail 'user1', @query = 'sp_configure'

D.    Send Results as an Attached File

This example sends the results of the query SELECT * from sysobjects as a text file attachment to user1. It includes a subject line for the mail and a message that will appear before the attachment. The @width option is used to prevent line breaks in the output lines.

xp_sendmail @recipients = 'user1', 
@query = 'select * from sysobjects',
@subject = 'SQL Server Report',
@message = 'The contents of sysobjects:',
@attach_results = 'TRUE', @width = 250

E.    Send Messages Longer than 255 Characters

This example shows how to send a message longer than 255 characters. Because the @message parameter is limited to the length of a varchar (as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @query parameter.

CREATE TABLE ##texttab (c1 text)
INSERT ##texttab values ('Put your long message here.')
DECLARE @cmd varchar(56)
SELECT @cmd = 'SELECT c1 FROM ##texttab
EXEC master.dbo.xp_sendmail 'user1', 
    @query = @cmd, @no_header= 'TRUE'
DROP TABLE ##texttab

Permission

Execute permission defaults to the system administrator, who can grant permission to other users.

See Also