xp_sendmail system procedure

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 = filename ] 
 [, 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 provided for Transact-SQL compatibility. It is not used by SQL Anywhere. 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.
attachname This LONG VARCHAR parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is NULL.
attach_result This INTEGER parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is 0.
echo_error This INTEGER parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is 1.
include_file This LONG VARCHAR parameter specifies an attachment file. The default is NULL.
no_column_header This INTEGER parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is 0.
no_output This INTEGER parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is 0.
width This INTEGER parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is 80.
separator This CHAR(1) parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is CHAR(9).
dbuser This LONG VARCHAR parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is guest.
dbname This LONG VARCHAR parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is master.
type This LONG VARCHAR parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. The default is NULL.
include_query This INTEGER parameter is provided for Transact-SQL compatibility. It is not used by SQL Anywhere. 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.
To set headers manually, set the content_type parameter to ASIS. When you do this, the xp_sendmail procedure assumes that the data passed to the message parameter is a properly formed email with headers, and does not add any additional headers. When specifying ASIS, you must set all the headers manually in the message parameter, even headers that would normally be filled in by passing data to the other parameters.

This function returns an INTEGER status code.

See Return codes for MAPI and SMTP system procedures.

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 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. For more information about MIME, see RFCs 2045-2049 ( [external link] http://www.ietf.org/).

Any attachment specified by the include_file parameter is sent as application/octet-stream MIME type, with base64 encoding, and must be present on the database server.

In SQL Anywhere 10.0.0 and later, email sent with an SMTP email system is encoded if the subject line contains characters that are not 7-bit ASCII. Also, email sent to an SMS-capable device may not be decoded properly if the subject line contains characters that are not 7-bit ASCII.

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.

You must have the SEND EMAIL system privilege.

This example uses SMTP to send a plain text message.

CALL xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
CALL xp_sendmail( recipient='jane.smith@sample.com',
                  subject='This is my subject line',
                  "message"='This text is the body of my email.\n' ); 
CALL xp_stopsmtp( );

This example uses SMTP to send an HTML formatted message with an attachment.

CALL xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
CALL xp_sendmail( recipient='jane.smith@sample.com',
                  subject='HTML mail example with attachment',
                  "message"='Plain text.<BR><BR><B>Bold text.</B><BR><BR>' ||
                            '<a href="www.sap.com">SAP Home Page</a>',
                  content_type = 'text/html',
                  include_file = '\\temp\\sendmail2.sql' ); 
CALL xp_stopsmtp( );

This example uses SMTP to send an inline HTML formatted message with an attachment.

CALL xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
CALL xp_sendmail( recipient='jane.smith@sample.com',
                  subject='Inline HTML mail example with attachment',
                  "message"='Content-Type: text/html;\nContent-Disposition: inline; \n\n' ||
                            'Plain text.<BR><BR><B>Bold text.</B><BR><BR>' ||
                            '<a href="www.sap.com">SAP Home Page</a>',
                  content_type = 'ASIS',
                  include_file = '\\temp\\sendmail3.sql' ); 
CALL xp_stopsmtp( );

This example uses SMTP to send an inline HTML formatted message with a signature and two attachments, one of which is a ZIP file.

BEGIN
DECLARE content LONG VARCHAR;

SET content = 
'Content-Type: multipart/mixed; boundary="xxxxx";\n' ||
'This part of the email should not be shown.  If this ' ||
'is shown then the email client is not MIME compatible\n\n' ||
'--xxxxx\n' ||
'Content-Type: text/html;\n' ||
'Content-Disposition: inline;\n\n' ||
'Plain text.<BR><BR><B>Bold text.</B><BR><BR>' ||
'<a href="www.sap.com">SAP Home Page</a>\n\n' ||
xp_read_file( '\\temp\\johndoe.sig.html' ) ||
'--xxxxx\n' ||
'Content-Type: application/zip; name="sendmail4.zip"\n' ||
'Content-Transfer-Encoding: base64\n' ||
'Content-Disposition: attachment; filename="sendmail4.zip"\n\n' ||
base64_encode( xp_read_file( '\\temp\\sendmail4.zip' ) ) ||
'\n\n' ||
'--xxxxx--\n'; 

CALL xp_startsmtp( 'doe@sample.com', 'corporatemail.sample.com' );
CALL xp_sendmail( recipient='jane.smith@sample.com',
                  subject='Inline HTML mail example with signature and 2 attachments',
                  "message"=content,
                  content_type = 'ASIS',
                  include_file = '\\temp\\sendmail4.sql' ); 
CALL xp_stopsmtp( );
END

xp_sendmail system procedure

Syntax

Arguments

Returns

Remarks

Privileges

See also

Example