Hvordan anvendes xp_sendmail

Er der ingen der kender
xp_sendmail?
Det er en stored procedure der ligger
i master databasen.
Så vidt jeg er orienteret skal det kunne lade sig gøre at sende mails gennem denne eller hvad?

Lars Linde

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.

Er sqlmail sat op på din server?

Hvis ikke, så følg anvisningen på
http://www.swynk.com/sqlhome/sqlmail.asp

Mr Modal

jeg synes det er så tyndt bare at paste tekst ind fra hjælpefilen kan .. manden kan vel læse det selv!

ok, jeg vil gerne give dig ret thomas, det kan godt se lidt tyndt med teksten fra en hjælpe fil, men det eneste jeg kan sige er, at den besvare spørgsmålet...!

/kan

Tak alle