Implementing error handling proceduresSQLRemote allows you to carry out some other process in addition to
logginga message if an error occurs. TheReplication_error database option
allowsyou to specify a stored procedure to be called by the Message Agent
whenan error occurs. Bydefault no procedure is called.
Theprocedure must have a single argument of type CHAR, VARCHAR, or
LONGVARCHAR. The procedure is called twice: once with the error
messageand once with the SQL statement that causes the error.
Whilethe option allows you to track and monitor errors in replication, you
muststill design them out of your setup: thisoption is not intended to
resolvesuch errors.
Forexample, the procedure could insert the errors into a table with the
currenttime and remote user ID, and this information can then replicate back
tothe consolidated database. Anapplication at the consolidated database can
createa report or send e-mail to an administrator when errors show up.
☞Forinformation on setting the REPLICATION_ERROR option, see
“SQLRemote options” on page 315.
Example: e-mailing notification of errors
Youmay wish to receive some notification at the consolidated database
whenthe Message Agent encounters errors. Thissection demonstrates a
methodto send Email messages to an administrator when an error occurs.
Astored procedure Thestored procedure for this example is called sp_LogReplicationError,
andis owned by the user cons. Tocause this procedure to be called in the
eventof an error, set the Replication_error database option using
InteractiveSQL or Sybase Central:
SET OPTION PUBLIC.Replication_error =
’cons.sp_LogReplicationError’
Thefollowing 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;
246