MAPI and SMTP system procedures

SQL Anywhere includes system procedures for sending electronic mail using the Microsoft Messaging API standard (MAPI) or the Internet standard Simple Mail Transfer Protocol (SMTP). These system procedures are implemented as extended system procedures: each procedure calls a function in an external DLL.

These procedures are owned by the dbo role. Users must be granted the EXECUTE privilege on these system procedures before they can use them.

To use the MAPI or SMTP system procedures, a MAPI or SMTP email system must be accessible from the database server computer.

The MAPI and SMTP system procedures are:

xp_startmail Starts a mail session in a specified mail account by logging on to the MAPI message system.
xp_startsmtp Starts a mail session in a specified mail account by logging on to the SMTP message system.
xp_sendmail Sends a mail message to specified users.
xp_stopmail Closes the MAPI mail session.
xp_stopsmtp Closes the SMTP mail session.
xp_get_mail_error_code Returns information about the most recent SMTP or MAPI error.
xp_get_mail_error_text Returns the text of the most recent SMTP error.

Return codes for MAPI and SMTP system procedures

The following codes can be returned by either the MAPI or the SMTP system procedures:

Return code	Meaning
-1	Unknown error¹
0	Success
1	An invalid parameter was supplied
2	Out of memory
3	xp_startmail or xp_startsmtp was not called
4	Bad host name
5	Connect error¹
6	Secure connection error¹
7	MAPI functions are not available¹

¹ Use the xp_get_mail_error_code system procedure to get additional information about a return code.

Example

The following procedure notifies a set of people that a backup has been completed. It does not check return codes.

CREATE PROCEDURE notify_backup( )
BEGIN
    CALL xp_startmail(  mail_user='ServerAccount',
                        mail_password='ServerPassword' );
    CALL xp_sendmail( recipient='IS Group',
                      subject='Backup',
                      "message"='Backup completed' );
    CALL xp_stopmail( );
END;

The following procedure notifies a set of people that a backup has been completed and includes error checking.

CREATE OR REPLACE PROCEDURE notify_backup_with_error_check( )
BEGIN
    DECLARE result_code INTEGER;
    DECLARE error_code INTEGER;
    DECLARE error_text LONG VARCHAR;
    SET result_code = xp_startmail(  'ServerAccount', -- mail_user
                                     'ServerPassword' -- mail_password
                                  );
    IF result_code = 0 THEN
         SET result_code = xp_sendmail( 'IS Group', --recipient
                                        'Backup', -- subject
                                        NULL, -- cc_recipient
                                        NULL, -- bcc_recipient
                                        NULL, -- query
                                        'Backup completed' -- message
                                      );
    ENDIF;
    IF result_code = 0 THEN
        SET result_code = xp_stopmail( );
    ENDIF;
    IF result_code = 0 THEN
        MESSAGE 'Backup completed message successully sent';
    ELSE
        SET error_code = xp_get_mail_error_code();
        SET error_text = xp_get_mail_error_text();
        MESSAGE 'Error:' || result_code || 
                ' Code:' || error_code || 
                ' Text:' || error_text;
    ENDIF;
END;;