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?
asked 03 Sep '11, 00:21
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.
answered 05 Sep '11, 03:19
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
PREPARE TO COMMIT operations
Connects and disconnects
Beginnings of transactions
DROP STATEMENT statements
Cursor open, close, and resume
May be it will logs all error into logging request file?
answered 05 Sep '11, 04:24