Receive email notification about remote database errors

Use the following procedure to send email notification when an error occurs at a remote database. You can use email or a paging system to receive the notifications.

 To set up SQL Remote to send email notification of errors (SQL)
  1. Use the SQL Anywhere 12 plug-in to connect to the consolidated database as user Cons.

  2. Create a stored procedure that notifies the DBA user by email that an error has occurred.

    For example, execute the following to create the sp_LogReplicationError procedure:



    CREATE PROCEDURE cons.sp_LogReplicationError
     ( IN error_text LONG VARCHAR )
    BEGIN
     DECLARE current_remote_user CHAR( 255 );
     SET current_remote_user = CURRENT REMOTE USER;
     // Log the error
     INSERT INTO cons.replication_audit
      ( remoteuser, errormsg )
     VALUES
      ( current_remote_user, error_text );
     COMMIT WORK;
     //Now notify the DBA by email that an error has occurred
     // on the consolidated database. The email should contain the error
     // strings that the SQL Remote Message Agent is passing to the procedure. 
     IF CURRENT PUBLISHER = 'cons' THEN
      CALL sp_notify_DBA( error_text );
     END IF
    END;
  3. Create a stored procedure that manages the sending of email.

    For example, execute the following statement to create the sp_notifiy_DBA procedure:



    CREATE PROCEDURE sp_notify_DBA( in msg long varchar)
    BEGIN
     DECLARE rc INTEGER;
     rc=call xp_startmail( mail_user='davidf' );
     //If successful logon to mail
     IF rc=0 THEN
     rc=call xp_sendmail(
       recipient='Doe, John; Smith, Elton',
       subject='SQL Remote Error',
       "message"=msg);
     //If mail sent successfully, stop
      IF rc=0 THEN
       call xp_stopmail()
      END IF
     END IF
    END;
  4. Set the replication_error database option to call the procedure that notifies the DBA by email that an error occurs.

    For example, execute the following statement to call the sp_LogReplicationError procedure when an error occurs:

    SET OPTION PUBLIC.replication_error =
     'cons.sp_LogReplicationError';
  5. Create an audit table.

    For example, execute the following to create the replication_audit table:

    CREATE TABLE replication_audit (
     id   INTEGER DEFAULT AUTOINCREMENT,
     pub  CHAR(30) DEFAULT CURRENT PUBLISHER,
     remoteuser CHAR(30),
     errormsg LONG VARCHAR,
     timestamp  DATETIME DEFAULT CURRENT TIMESTAMP,
     PRIMARY KEY (id,pub)
    );

    The following table describes the columns of the replication_audit table:

    Column Description
    pub Current publisher of the database (identifies the database in which the publisher was inserted).
    remoteuser Remote user applying the message (identifies the database from which the remote user came from).
    errormsg Error message passed to the replication_error procedure.
  6. Test your procedures.

    For example, insert a row on the consolidated database that uses the same primary key as a row on a remote database. When this row from the consolidated database is replicated to the remote database, a primary key conflict error occurs and:

    • The remote database SQL Remote Message Agent (dbremote) prints the following message to its output log:

      Received message from "cons" (0-0000000000-0) 
      SQL statement failed: (-193) primary key for table 'reptable' is not unique 
      INSERT INTO cons.reptable( id,text,last_contact ) 
      VALUES (2,'dave','1997/apr/21 16:02:38.325') 
      COMMIT WORK
    • The following INSERT statement is sent to the consolidated database:



      INSERT INTO cons.replication_audit
       ( id,
         pub,
         remoteuser,
         errormsg,
         "timestamp")
      VALUES
       ( 1,
        'cons',
        'sales',
        'primary key for table ''reptable'' is not unique (-193)',
        '1997/apr/21 16:03:13.836');
      COMMIT WORK;
    • An email is sent to John Doe and Elton Smith with the following message:

      primary key for table 'reptable' is not unique (-193) 
      INSERT INTO cons.reptable( id,text,last_contact )
                  VALUES (2,'dave','1997/apr/21 16:02:52.605')
 See also