|
Sends an email message.
xp_sendmail(
recipient = mail-address
[, subject = subject ]
[, cc_recipient = mail-address ]
[, bcc_recipient = mail-address ]
[, query = sql-query ]
[, "message" = message-body ]
[, attachname = attach-name ]
[, attach_result = attach-result ]
[, echo_error = echo-error ]
[, include_file = file-name ]
[, no_column_header = no-column-header ]
[, no_output = no-output ]
[, width = width ]
[, separator = separator-char ]
[, dbuser = user-name ]
[, dbname = db-name ]
[, type = type ]
[, include_query = include-query ]
[, content_type = content-type ]
)
Some arguments supply fixed values and are available for use to ensure Transact-SQL compatibility, as noted below.
recipient This LONG VARCHAR parameter specifies the recipient mail address. When specifying multiple recipients, each mail address must be separated by a semicolon.
subject This LONG VARCHAR parameter specifies the subject field of the message. The default is NULL.
cc_recipient This LONG VARCHAR parameter specifies the cc recipient mail address. When specifying multiple cc recipients, each mail address must be separated by a semicolon. The default is NULL.
bcc_recipient This LONG VARCHAR parameter specifies the bcc recipient mail address. When specifying multiple bcc recipients, each mail address must be separated by a semicolon. The default is NULL.
query This LONG VARCHAR is for use with Transact-SQL. The default is NULL.
"message" This LONG VARCHAR parameter specifies the message contents. The default is NULL. The "message" parameter name requires double quotes around it because MESSAGE is a reserved word. See Reserved words.
attachname This LONG VARCHAR parameter is for use with Transact-SQL. The default is NULL.
attach_result This INT parameter is for use with Transact-SQL. The default is 0.
echo_error This INT parameter is for use with Transact-SQL. The default is 1.
include_file This LONG VARCHAR parameter specifies an attachment file. The default is NULL.
no_column_header This INT parameter is for use with Transact-SQL. The default is 0.
no_output This INT parameter is for use with Transact-SQL. The default is 0.
width This INT parameter is for use with Transact-SQL. The default is 80.
separator This CHAR(1) parameter is for use with Transact-SQL. The default is CHAR(9).
dbuser This LONG VARCHAR parameter is for use with Transact-SQL. The default is guest.
dbname This LONG VARCHAR parameter is for use with Transact-SQL. The default is master.
type This LONG VARCHAR parameter is for use with Transact-SQL. The default is NULL.
include_query This INT parameter is for use with Transact-SQL. The default is 0.
content_type This LONG VARCHAR parameter specifies the content type for the "message" parameter (for example, text/html, ASIS, and so on). The default is NULL. The value of content_type is not validated; setting an invalid content type results in an invalid or incomprehensible email being sent.
DBA authority required
Must have executed xp_startmail to start an email session using MAPI, or xp_startsmtp to start an email session using SMTP.
If you are sending mail using MAPI, the content_type parameter is not supported.
Not supported on NetWare.
xp_sendmail is a system procedure that sends an email message to the specified recipients once a session has been started with xp_startmail or xp_startsmtp. The procedure accepts messages of any length. The argument values for xp_sendmail are strings. The length of each argument is limited to the amount of available memory on your system.
The content_type argument is intended for users who understand the requirements of MIME email. xp_sendmail accepts ASIS as a content_type. When content_type is set to ASIS, xp_sendmail assumes that the message body ("message") is already a properly formed email with headers, and does not add any additional headers. Specify ASIS to send multipart messages containing more than one content type. See RFCs 2045-2049 (![[external link]](gif/external.gif)
http://www.ietf.org/) for more information on MIME.
Attachments specified by the include_file parameter are sent as application/octet-stream MIME type, with base64 encoding, and must be present on the database server.
See Return codes for MAPI and SMTP system procedures.
The following call sends a message to the user ID Sales Group containing the file prices.doc as a mail attachment:
CALL xp_sendmail( recipient='Sales Group',
subject='New Pricing',
include_file = 'C:\\DOCS\\PRICES.DOC' )
The following sample program shows various uses of the xp_sendmail system procedure, as described in the example itself:
BEGIN
DECLARE to_list LONG VARCHAR;
DECLARE email_subject CHAR(256);
DECLARE content LONG VARCHAR;
DECLARE uid CHAR(20);
set to_list='test_account@mytestdomain.com';
set email_subject='This is a test';
set uid='test_sender@mytestdomain.com';
// Call xp_startsmtp to start an SMTP email session
call xp_startsmtp( uid, 'mymailserver.mytestdomain.com' );
// Basic email example
set content='This text is the body of my email.\n';
call xp_sendmail( recipient=to_list,
subject=email_subject,
"message"=content );
// Send email containing HTML using the content_type parameter,
// as well as including an attachment with the include_file
// parameter
set content='Plain text.<BR><BR><B>Bold text.</B><BR><BR><a href="www.iAnywhere.com">iAnywhere
Home Page</a></B><BR><BR>';
call xp_sendmail( recipient=to_list,
subject=email_subject,
"message"=content,
content_type = 'text/html',
include_file = 'test.zip' );
// Send email "ASIS". Here the content-type has been specified
// by the user as part of email body. Note the attachment can
// also be done separately
set content='Content-Type: text/html;\nContent-Disposition: inline; \n\nThis text
is not bold<BR><BR><B>This text is bold</B><BR><BR><a href="www.iAnywhere.com">iAnywhere Home
Page</a></B><BR><BR>';
call xp_sendmail( recipient=to_list,
subject=email_subject,
"message"=content,
content_type = 'ASIS',
include_file = 'test.zip' );
// Send email "ASIS" along with an include file. Note that
// "message" contains the information for another attachment
set content = 'Content-Type: multipart/mixed; boundary="xxxxx";\n';
set content = content || 'This part of the email should not be shown. If this is shown
then the email client is not MIME compatible\n\n';
set content = content || '--xxxxx\n';
set content = content || 'Content-Type: text/html;\n';
set content = content || 'Content-Disposition: inline;\n\n';
set content = content || 'This text is not bold<BR><BR><B>This text is bold</B><BR>
<BR><a href="www.iAnywhere.com">iAnywhere Home Page</a></B><BR><BR>\n\n';
set content = content || '--xxxxx\n';
set content = content || 'Content-Type: application/zip; name="test.zip"\n';
set content = content || 'Content-Transfer-Encoding: base64\n';
set content = content || 'Content-Disposition: attachment; filename="test.zip"\n\n';
// Encode the attachment yourself instead of adding this one in
// the include_file parameter
set content = content || base64_encode( xp_read_file( 'othertest.zip' ) ) || '\n\n';
set content = content || '--xxxxx--\n';
call xp_sendmail( recipient=to_list,
subject=email_subject,
"message"=content,
content_type = 'ASIS',
include_file = 'othertest.zip' );
// end the SMTP session
call xp_stopsmtp();
END