How to log all database server errors?

[Former Member]

How to log all database server errors like primary key violation error, column not found...into separate table on the same database with error message, user id, date and time. Is there any system event to log the errors?

[VolkerBarth]

Cf. Breck's blog article...

[Former Member]

I am not try it, but documentation describe such functionality:

sa_server_option system procedure

RequestLogFile The name of the file used to record request information. Specifying an empty string stops logging to the request log file. If request logging is enabled but the request log file was not specified or has been set to an empty string, the server logs requests to the Server Messages window. Any backslash characters in the path must be doubled as this is a SQL string. See -zo server option.

RequestLogging This call turns on logging of individual SQL statements sent to the database server for use in troubleshooting, in conjunction with the database server -zr and -zo options. Values can be combinations of the following, separated by either a plus sign (+), or a comma:

SQL enables logging of the following:

START DATABASE statements

STOP DATABASE statements

STOP ENGINE statements

Statement preparation and execution

EXECUTE IMMEDIATE statement

Option settings

COMMIT statements

ROLLBACK statements

PREPARE TO COMMIT operations

Connects and disconnects

Beginnings of transactions

DROP STATEMENT statements

Cursor explanations

Cursor open, close, and resume

Errors

May be it will logs all error into logging request file?

[Former Member]

it works! so you can later parse log file for errors. Good luck!

[VolkerBarth]

RLL is a helpful feature, however as it does log all queries (when configured that way) - both successful and errornous - it may slow down the server performance. Therefore I would not recommend that as a "general error logging facility"...

[VolkerBarth]

I don't have a real answer, just a few hints...

What is the goal of this logging - should it also log errors in ad-hoc queries from DBISQL and the like?

(Personally, I often need a few attempts to get ad-hoc queries working - wrong syntax, typos in column names etc. These lead to error messages, too - but I wouldn't expect any worth in logging these "query attempts" - besides showing me what I already know:))

I'm not aware of a general-error-logging facility. I guess usually each application will use its own scheme of logging. E.g. moszt (or all?) of the builtin SQL Anywhere's utilities have the -o option to log their output (including success and error messages) to a file.

The default error handling is to reject the failing statement and notify the calling environment with an according error code and message. When using stored procedures, triggers or batches, you can define error handlers that do what you want, including logging these errors in a user-table (which might need a separate transaction when the original transaction is rolled back). However, AFAIK, there's no way to specify a general event handler.