SQL Anywhere Mobile - Automated "warning/error" emails?

Author

Automated "warning/error" emails?

Mad Vlad

2006-04-04, 7:42 am

ASA 9.0.2.3221

We recently had a situation where one remote database stopped synch'ing,
well, not stopped, but at each cycle, there was an error, which caused it
all to rollback. Once the error was traced down and fixed, everything went
through fine. The point is that the error wasn't noticed for 2-3 days.

I've been reading through the docs, and they quote some suggestions about
handle_error and report_error scripts, which log the error code and the
description in a new table. Not a bad system, but that still requires sys
admin having to re-view contents of this table on a daily (hournly?) basis.

We thought it would be neat if you could somehow, fire off an email to a
specific address whenever in the log window you have a (W)arning or (E)rror,
with the text of the error? In fact, on page 62 of User's Guide it does
indeed mention "Send an e-mail message", and refers to page 220 of Sync
Guide, but says no more on the subject. Most examples just show how to log
it to the error table. Are there any examples of procedures that would do
this sort of thing?

Cheers,
Vlad

Reg Domaratzki $iAnywhere Solutions$

2006-04-04, 9:32 am

Assuming an ASA consolidated, you can log the message into the table and use
a trigger to send an e-mail. This gives the best of both worlds, since you
can look back at the table and send instant notifications :

call ml_add_connection_sc
ript( 'ver1', 'report_error', 'insert into
sync_error values( DEFAULT, ?, ?, ?, ?, ?, DEFAULT )' );

create table sync_error (
pkey bigint default autoincrement primary key,
action_code integer,
error_code integer,
error_message long varchar,
ml_username varchar(128),
table_name varchar(128),
time_inserted timestamp default current timestamp
);

create trigger ai_sync_error after insert on sync_error
referencing new as nr for each row
begin
declare @msg long varchar;
set @msg = 'An error has been reported!\n\n';
set @msg = @msg || 'pkey : ' || nr.pkey || '\n';
set @msg = @msg || 'action_code : ' || nr.action_code|| '\n';
set @msg = @msg || 'error_code : ' || nr.error_code || '\n';
set @msg = @msg || 'error_message : ' || nr.error_message || 'n';
set @msg = @msg || 'ml_username : ' || nr.ml_username|| '\n';
set @msg = @msg || 'table_name : ' || nr.table_name || '\n\n\n';
set @msg = @msg || 'Current Time : ' || nr.time_inserted || '\n';

call xp_startsmtp( smtp_sender = 'your@email.com', smtp_server =
'your.mailserver.com' );
call xp_sendmail( recipient = 'admin@email.com', subject = 'Error at
MobiLink Server', "message" = @msg );
call xp_stopsmtp();
end;

If you're not using an ASA consolidated, so you don't have access to
xp_startsmp/xp_sendmail/xp_stopsmtp, you could always use a Java or .Net
class as your report_error event that generates and sends an e-mail.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in message
news:44325056$1@foru
ms-1-dub...
> ASA 9.0.2.3221
>
> We recently had a situation where one remote database stopped synch'ing,
> well, not stopped, but at each cycle, there was an error, which caused it
> all to rollback. Once the error was traced down and fixed, everything went
> through fine. The point is that the error wasn't noticed for 2-3 days.
>
> I've been reading through the docs, and they quote some suggestions about
> handle_error and report_error scripts, which log the error code and the
> description in a new table. Not a bad system, but that still requires sys
> admin having to re-view contents of this table on a daily (hournly?)
basis.
>
> We thought it would be neat if you could somehow, fire off an email to a
> specific address whenever in the log window you have a (W)arning or
(E)rror,
> with the text of the error? In fact, on page 62 of User's Guide it does
> indeed mention "Send an e-mail message", and refers to page 220 of Sync
> Guide, but says no more on the subject. Most examples just show how to log
> it to the error table. Are there any examples of procedures that would do
> this sort of thing?
>
>
> Cheers,
> Vlad
>
>

Mad Vlad

2006-04-04, 9:32 am

Whoa! Fan-tastic! Yes, it's ASA.

Thanks,
Vlad

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:44327e3b$1@foru
ms-1-dub...
> Assuming an ASA consolidated, you can log the message into the table and
> use
> a trigger to send an e-mail. This gives the best of both worlds, since
> you
> can look back at the table and send instant notifications :
>
> call ml_add_connection_sc
ript( 'ver1', 'report_error', 'insert into
> sync_error values( DEFAULT, ?, ?, ?, ?, ?, DEFAULT )' );
>
> create table sync_error (
> pkey bigint default autoincrement primary key,
> action_code integer,
> error_code integer,
> error_message long varchar,
> ml_username varchar(128),
> table_name varchar(128),
> time_inserted timestamp default current timestamp
> );
>
> create trigger ai_sync_error after insert on sync_error
> referencing new as nr for each row
> begin
> declare @msg long varchar;
> set @msg = 'An error has been reported!\n\n';
> set @msg = @msg || 'pkey : ' || nr.pkey || '\n';
> set @msg = @msg || 'action_code : ' || nr.action_code|| '\n';
> set @msg = @msg || 'error_code : ' || nr.error_code || '\n';
> set @msg = @msg || 'error_message : ' || nr.error_message || 'n';
> set @msg = @msg || 'ml_username : ' || nr.ml_username|| '\n';
> set @msg = @msg || 'table_name : ' || nr.table_name || '\n\n\n';
> set @msg = @msg || 'Current Time : ' || nr.time_inserted || '\n';
>
> call xp_startsmtp( smtp_sender = 'your@email.com', smtp_server =
> 'your.mailserver.com' );
> call xp_sendmail( recipient = 'admin@email.com', subject = 'Error at
> MobiLink Server', "message" = @msg );
> call xp_stopsmtp();
> end;
>
> If you're not using an ASA consolidated, so you don't have access to
> xp_startsmp/xp_sendmail/xp_stopsmtp, you could always use a Java or .Net
> class as your report_error event that generates and sends an e-mail.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
> -> Choose SQL Anywhere Studio
> -> Set filter to "Display ALL platforms IN ALL MONTHS"
>
>
> "Mad Vlad" <vlad@NOSPAMpcr.ltd.uk> wrote in message
> news:44325056$1@foru
ms-1-dub...
> basis.
> (E)rror,
>
>

Greg Fenton

2006-04-04, 9:32 am

Reg Domaratzki (iAnywhere Solutions) wrote:
> Assuming an ASA consolidated, you can log the message into the table and use
> a trigger to send an e-mail. This gives the best of both worlds, since you
> can look back at the table and send instant notifications :
>

Or have report_error call a stored proc that does the insert *and* send
an email....no need for triggers [I avoid triggers like the avian flu].

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Sponsored Links