I want to find a bit more about recording if a JOB fails, or reports an Error message.

I see the "LogEvents" table, and the SET LOG MESSAGE SQL command. This seems to be very useful for logging messages within JOBs or FUNCTIONS.

I am assuming code in this form within a JOB would be suitable & would be certain to run:

-- some SQL code here to set up stmt:
  EXECUTE stmt;
EXCEPTION
 SET LOG MESSAGE ''Error Generated in Job: XXX. Error Code: ''+CAST(ERRORCODE() as VARCHAR(10));

I am a bit confused:

* There is no need for a TRY or END? The EXCEPTION does not seem to have scope ...
* Will the error be generated & logged event if the original exception occurs in an custom function called by the job?

Adam,

<< I see the "LogEvents" table, and the SET LOG MESSAGE SQL command. This
seems to be very useful for logging messages within JOBs or FUNCTIONS. >>

The SET LOG MESSAGE statement isn't exactly what you think it is.  It
doesn't log messages to the system Logged Events, rather it simply spits out
a string that goes back to the client, or nowhere if you're executing a job
or a function/procedure inside of another function/procedure/script/job.

<< I am assuming code in this form within a JOB would be suitable & would be
certain to run: >>

What you want instead of the SET LOG MESSAGE is just a simple table that you
can write out rows to when you encounter an error.  Just use something like
this:

BEGIN
   EXECUTE stmt;
EXCEPTION
  EXECUTE IMMEDIATE 'INSERT INTO MyLogTable (LogErrorCode, LogErrorMsg)
VALUES (?, ?)' USING ERRORCODE(), ERRORMSG();
END

<< * There is no need for a TRY or END? The EXCEPTION does not seem to have
scope ... >>

See here for the structure:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=EXCEPTION

The reason that BEGIN is used instead of TRY is so that you can simply stick
an EXCEPTION block directly into the outer BEGIN..END pair of a script,
function, procedure, or job.

<< * Will the error be generated & logged event if the original exception
occurs in an custom function called by the job? >>

Sure, all exceptions bubble up automatically, just like with Delphi or any
other type of code.

--
Tim Young
Elevate Software
www.elevatesoft.com