Data Replication with SQL Remote
   PART 3. SQL Remote Administration
     CHAPTER 11. Administering SQL Remote for Adaptive Server Anywhere       

Error reporting and handling

This section describes how errors are reported and handled by the Message Agent.

  Default error handling

The default action taken by the Message Agent when an error occurs is to record the fact in its log output. The Message Agent sends log output to a window or a log file recording its operation. By default, log output is sent to the window only; the -o command-line option sends output to a log file as well.

The Message Agent log includes the following:

• Listing of messages applied.

• Listing of failed SQL statements.

• Listing of other errors.

UPDATE conflicts are not errors 

UPDATE conflicts are not errors, and so are not reported in the Message Agent output.

  Ignoring errors

There may be exceptional cases where you wish to allow an error encountered by the Message Agent when applying SQL statements to go unreported. This may arise when you know the conditions under which the error occurs and are sure that it does not produce inconsistent data and that its consequences can safely be ignored.

To allow errors to go unreported, you can create a BEFORE trigger on the action that causes the known error. The trigger should signal the REMOTE_STATEMENT_FAILED SQLSTATE or SQLCODE value.

For example, if you wish to quietly fail INSERT statements on a table that fail because of a missing referenced column, you could create a BEFORE INSERT trigger that signals the REMOTE_STATEMENT_FAILED SQLSTATE when the referenced column does not exist. The INSERT statement fails, but the failure is not reported in the Message Agent log.

  Implementing error handling procedures

SQL Remote allows you to carry out some other process in addition to logging a message if an error occurs. The Replication_error database option allows you to specify a stored procedure to be called by the Message Agent when an error occurs. By default no procedure is called.

The procedure must have a single argument of type CHAR, VARCHAR, or LONG VARCHAR. The procedure is called twice: once with the error message and once with the SQL statement that causes the error.

While the option allows you to track and monitor errors in replication, you must still design them out of your setup: this option is not intended to resolve such errors.

For example, the procedure could insert the errors into a table with the current time and remote user ID, and this information can then replicate back to the consolidated database. An application at the consolidated database can create a report or send e-mail to an administrator when errors show up.

     For information on setting the REPLICATION_ERROR option, see SQL Remote options.

  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 

The audit table is 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:

Column	Description
pub	Current publisher of the database (lets you know at what database it was inserted)
remoteuser	Remote user applying the message (lets you know what database it came from)
errormsg	Error message passed to the Replication_error procedure

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