-----------------------------------------------------------
New Message on MumbaiUserGroup
-----------------------------------------------------------
From: navnath_yadav
Message 2 in Discussion
u have use sql mail with store procedure
Transact-SQL Reference
xp_sendmail
Sends a message and a query result set attachment to the specified recipients.
Syntax
xp_sendmail [EMAIL PROTECTED] =] 'recipients [;...n]'}
[,[EMAIL PROTECTED] =] 'message']
[,[EMAIL PROTECTED] =] 'query']
[,[EMAIL PROTECTED] =] 'attachments [;...n]']
[,[EMAIL PROTECTED] =] 'copy_recipients [;...n]'
[,[EMAIL PROTECTED] =] 'blind_copy_recipients [;...n]'
[,[EMAIL PROTECTED] =] 'subject']
[,[EMAIL PROTECTED] =] 'type']
[,[EMAIL PROTECTED] =] 'attach_value']
[,[EMAIL PROTECTED] =] 'output_value']
[,[EMAIL PROTECTED] =] 'header_value']
[,[EMAIL PROTECTED] =] width]
[,[EMAIL PROTECTED] =] 'separator']
[,[EMAIL PROTECTED] =] 'echo_value']
[,[EMAIL PROTECTED] =] 'user']
[,[EMAIL PROTECTED] =] 'database']
Arguments
[EMAIL PROTECTED] =] 'recipients [;...n]'
Is the semicolon-separated list of the recipients of the mail.
n
Is a placeholder indicating that more than one recipient, copy_recipient, or
blind_copy_recipient can be specified.
[EMAIL PROTECTED] =] 'message'
Is the message to be sent. message can be up to 8,000 bytes.
[EMAIL PROTECTED] =] 'query'
Is a valid Microsoft® SQL Server query, the result of which is sent in mail.
xp_sendmail uses a bound connection for the query parameter. The query
connection made by SQL Mail 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. query can
be up to 8,000 bytes.
[EMAIL PROTECTED] =] 'attachments [;...n]'
Is a semicolon-separated list of files to attach to the mail message.
[EMAIL PROTECTED] =] 'copy_recipients [;...n]'
Is the semicolon-separated list identifying the recipients of a copy of the
mail (cc:'ing).
[EMAIL PROTECTED] =] 'blind_copy_recipients [;...n]'
Is an optional semicolon-separated list identifying recipients of a blind copy
of the mail (bcc:'ing).
[EMAIL PROTECTED] =] 'subject'
Is an optional parameter specifying the subject of the mail. If subject is not
specified, SQL Server Message is the default.
[EMAIL PROTECTED] =] 'type'
Is the input message type based on the MAPI mail definition:
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.
[EMAIL PROTECTED] =] 'attach_value'
Is an optional parameter specifying the result 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 file name in attachments is
used as the file name for the results. If attachments is NULL, a file name is
generated with a .txt extension. The default is FALSE, which means that the
result set is appended to the message.
[EMAIL PROTECTED] =] 'output_value'
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.
[EMAIL PROTECTED] =] 'header_value'
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.
[EMAIL PROTECTED] =] width
Is an optional parameter setting the line width of the output text for a query.
This parameter is identical to the /w parameter in the isql utility. For
queries producing long output rows, use width with attach_results to send the
output without line breaks in the middle of output lines. The default width is
80 characters.
[EMAIL PROTECTED] =] 'separator'
Is the column-separator string for each column of the result set. By default,
the column-separator is a blank space. Use of a column-separator allows easier
accessibility of the result set from spreadsheets and other applications. For
example, use separator with attach_results to send files with comma-separated
values.
[EMAIL PROTECTED] =] 'echo_value'
When true, causes SQL Mail 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 is appended to the mail message.
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.
[EMAIL PROTECTED] =] 'user'
Is the security context in which the query should be run. If user is not
specified, the security context defaults to that of the user executing
xp_sendmail.
[EMAIL PROTECTED] =] 'database'
Is the database context in which the query should be run. The default is NULL,
which means the user is placed in the default database.
Return Code Values
0 (success) or 1 (failure)
Result Sets
xp_sendmail returns this message:
Mail sent.
Remarks
The SQL Mail 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 SQL Mail session automatically, see Configuring Mail
Profiles. One SQL Mail 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. SQL Mail 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 SQL Mail 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 SQL Mail 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 SQL Mail (Transact-SQL).
Permissions
Execute permissions for xp_sendmail default to members of the db_owner fixed
database role in the master database and members of the sysadmin fixed server
role, but can be granted to other users.
Examples
A. Use xp_sendmail with no variables
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.'
B. Use xp_sendmail with variables
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'
C. Send results
This example sends the results of the sp_configure to Robert King.
EXEC xp_sendmail 'robertk', @query = 'sp_configure'
D. Send results as an attached file
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
E. Send messages longer than 7,990 characters
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
See Also
sp_processmail
System Stored Procedures (SQL Mail Extended Procedures)
xp_deletemail
xp_findnextmsg
xp_readmail
xp_startmail
xp_stopmail
©1988-2000 Microsoft Corporation. All Rights Reserved.
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/MumbaiUserGroup/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member
Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you
received this message by mistake, please click the "Remove" link below. On the
pre-addressed e-mail message that opens, simply click "Send". Your e-mail
address will be deleted from this group's mailing list.
mailto:[EMAIL PROTECTED]
