I am looking to see if there is a way to trap for errors when using the
command EXECUTE IMMEDIATE in an event. I use it to perform hourly
backups of an ASA database and would like to have the event email me if
the backup fails. The code I have looks like this:

IF HOUR(current time) < 1 THEN
SET backup_dir=backup_dir || '\\' || day_of_week;
SET backup_stmt='BACKUP DATABASE DIRECTORY ''' || backup_dir || '''
';
SET backup_stmt=backup_stmt || 'WAIT BEFORE START ' || 'WAIT AFTER
END ' || 'TRANSACTION LOG TRUNCATE ';
SET do_validation=1

ELSE

SET backup_dir=backup_dir || '\\' || day_of_week;
SET cmdshell_stmt='xp_cmdshell(''IF not exist ' || backup_dir || '
md ' || backup_dir || ''')';
EXECUTE immediate cmdshell_stmt;
SET backup_dir=backup_dir || '\\' || time_of_day;
SET backup_stmt='BACKUP DATABASE DIRECTORY ''' || backup_dir || '''
';
SET backup_stmt=backup_stmt || 'WAIT BEFORE START ' || 'WAIT AFTER
END ' || 'TRANSACTION LOG TRUNCATE ' || 'TRANSACTION LOG ONLY'

END IF;

EXECUTE immediate backup_stmt;

Thanks in advance

Use EXCEPTION. Here is a template...

--------------------------------
-- To find error in "some code"

BEGIN

DECLARE @sqlcode INTEGER;
DECLARE @sqlstate VARCHAR ( 5 );
DECLARE @errormsg VARCHAR ( 32767 );

"other code"

BEGIN

"some code"

EXCEPTION
WHEN OTHERS THEN
SELECT SQLCODE, SQLSTATE, ERRORMSG()
INTO @sqlcode, @sqlstate, @errormsg;
MESSAGE STRING (
'EXCEPTION in xxx at ',
CURRENT TIMESTAMP,
': SQLCODE = ', @sqlcode,
', SQLSTATE = ', @sqlstate,
', ERRORMSG() = ', @errormsg )
TO CONSOLE;
END;

"other code"

END;


Breck

On 24 Sep 2009 10:07:29 -0700, Ben Baker <bbaker (AT) nspirehealth (DOT) com>
wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com