Example: Emailing notification of errors

You may want 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 by email that an error has occurred
 // on the consolidated database. The email should contain the error
 // strings that 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:

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

Send feedback about this page via email or DocCommentXchange