xp_sendmail system procedure

Sends an email message.

Syntax

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 ]
)

Arguments

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.

Permissions

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.

Remarks

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

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.

Return codes

See Return codes for MAPI and SMTP system procedures.

Example

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

Send feedback about this page via email or DocCommentXchange