Sends a message and a query result set attachment to the specified recipients.
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']
IP[M | C].Vendorname.subclass
If type is NULL, message types beginning with IPM appear in the inbox of the mail client and are found or read by xp_findnextmsg. 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 more information about using custom message types, see the Microsoft Windows NT Resource Kit or the Microsoft Mail Technical Reference, available separately.
Note When 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.
0 (success) or 1 (failure)
xp_sendmail returns this message:
Mail sent.
The SQLMail session must be started prior to executing xp_sendmail. Sessions can be started either automatically or with xp_startmail. For more information about setting up a SQLMail session automatically, see Setting Up SQLAgentMail. 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 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 issuing xp_sendmail.
Note query can be blocked by a lock held by the client connection issued xp_sendmail. For example, if you are updating a table within a transaction and you create a trigger for update that attempts to select the same updated row information as the query parameter, the SQLMail connection is blocked by the exclusive lock held on row by the initial client connection.
xp_sendmail runs in SQL Server’s security context, which is a local administrator account by default. A valid user of xp_sendmail can access files for attachment to a mail message in an administrator’s security context. If nonsystem administrator users must access xp_sendmail and you want to guard against unsecured access to attachment files, the system administrator can create a stored procedure that calls xp_sendmail and provides the needed functionality but does not expose the attachments parameter. This stored procedure must be defined in the master database. The system administrator then grants execute permission on the stored procedure to the necessary users without granting permission to the underlying xp_sendmail procedure.
xp_sendmail sends a message and a query result set or an attachment to specified recipients, and uses a bound connection for the query parameter. The query connection made by SQLMail is not blocked by locks held by the client that issues the xp_sendmail request. This makes xp_sendmail easier to use from within triggers. The query statement, however, cannot refer to the logical inserted and deleted tables that are only available within a trigger.
Note An access violation can result from an attempt to execute xp_sendmail when the post office and address book are on a file share that the MSSQLServer service cannot access due to inadequate permissions.
For more information about using a stored procedure for calling xp_sendmail, see How to use SQLMail (Transact-SQL).
Execute permissions for xp_sendmail default to the member of the sysadmin fixed server role but can be granted to other users.
This example sends a message to user Robert King (e-mail is robertk) that the master database is full.
EXEC xp_sendmail 'robertk', 'The master database is full.'
This example sends the message to users Robert King and Laura Callahan (e-mail is laurac), with copies sent to Anne Dodsworth (e-mail is anned) and Michael Suyama (e-mail is michaels). It also specifies a subject line for the message.
EXEC xp_sendmail @recipients = 'robertk;laurac',
@message = 'The master database is full.',
@copy_recipients = 'anned;michaels',
@subject = 'Master Database Status'
This example sends the results of the sp_configure to Robert King.
EXEC xp_sendmail 'robertk', @query = 'sp_configure'
This example sends the results of the query SELECT * FROM INFORMATION_SCHEMA.TABLES as a text file attachment to Robert King. It includes a subject line for the mail and a message that will appear before the attachment. The @width parameter is used to prevent line breaks in the output lines.
EXEC xp_sendmail @recipients = 'robertk',
@query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES',
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'TRUE', @width = 250
This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, 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)
SET @cmd = 'SELECT c1 FROM ##texttab'
EXEC master.dbo.xp_sendmail 'robertk',
@query = @cmd, @no_header= 'TRUE'
DROP TABLE ##texttab
| sp_processmail | xp_startmail |
| xp_deletemail | xp_stopmail |
| xp_findnextmsg | System Stored Procedures (SQL Mail Extended Procedures) |
| xp_readmail |