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.
Ny bruger
Nybegynder
Opret
Preview
