SQL Remote User's Guide
  Administering SQL Remote for Adaptive Server Anywhere
    Error reporting and handling
      Implementing error handling procedures

Example: e-mailing notification of errors

You may wish to receive some notification at the consolidated database when the Message Agent encounters errors. This section demonstrates a method to send Email messages to an administrator when an error occurs.

A stored procedure 

The stored procedure for this example is called sp_LogReplicationError, and is owned by the user cons. To cause this procedure to be called in the event of an error, set the Replication_error database option using Interactive SQL or Sybase Central:

SET OPTION PUBLIC.Replication_error =
   'cons.sp_LogReplicationError'

The following stored procedure implements this notification:

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 an error has occurred
   // using email. We only want this information if    
 // the error occurred on the consolidated database
   // We want the email to contain the error strings    
 // the Message Agent is passing to the procedure
   IF CURRENT PUBLISHER = 'cons' THEN
      CALL sp_notify_DBA( error_text );
   END IF
END;

The stored procedure calls another stored procedure to manage the sending of Email:

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; John, Elton',
         subject='SQL Remote Error',
         "message"=msg);
   //If mail sent successfully, stop
      IF rc=0 THEN
         call xp_stopmail()
      END IF
   END IF
END;

An audit table 

An audit table could be defined as follows:

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 columns have the following meaning:

Here is a sample insert into the table from the above error:

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

Since Adaptive Server Anywhere supports calling external DLLs from stored procedures you can also design a paging system, instead of using Email.

An example of an error 

For example, if a row is inserted at the consolidated using the same primary key as one inserted at the remote, the Message Agent displays the following errors:

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 messages that arrived in Doe, John and Elton, John's email each had a subject of SQL Remote Error:

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