December 15, 2002 at 2:44 pm
Hello everybody,
Your advices and your experiences could help me. In fact, I’m trying to design the better Error System for my need in SQL Server 2000. What I mean by “Error System” ?
Here is the requirements:
- The main goals is to have a system which display nice message for the user when an error happen.
- When an error happen, we must have the possibility to log it into a table.
- The error must be managed into the stored procedure
- It exists 2 kind of errors : System (managed by the DB itself) and Business
- The Business error must be managed into several languages
- If an error occurs, a kind of error analyzer must be launch to give back more information as possible on the higher level (the caller). It means for example say that the insert failed because the “First_Name” couldn’t be null.
- The higher caller manage the transaction if it’s needed
- The errors must be catch able by the higher level. It means than the caller could be able to catch the error and continue to live and manage it in it’s way.
- Manage error in Function, Trigger are not required for the first version, but all advices are welcome
The actor in this design:
- Errors Log Table
- Business Error Reference Table
- Business Error Reference Translation
- Business Error
- System Error
- Stored Procedures
- Transaction
What are the tools that SQL Server give us :
- @@ERROR
- @@ROWCOUNT
- Output Parameters
- Stored Procedure to reuse code
Technical Problem
- If we store information concerning the error, it should be done after the rollback. For the moment, I haven’t find a way to excuse a statement outside the current transaction. It means that you must give information concerning the error to the higher level
- If you make a select statement inside a stored procedure even if you rollback the transaction a recordset is send back to VB for example. It could be a benefit and a disadvantage.
Current solution that I’ve found
3 tables :
ERRORS_LOG : contains all information concerning the errors happened.
ERRORS : contains only each business error NB
ERRORS_TL (Translation) : contains the translation of each Business Error in any existing languages
1 stored procedures :
INS_ERRORS : this is the stored procedure that it will be used when we want store info into the ERRORS_LOG table
Stored Procedures conventions
- We have almost a stored procedure by table and by statement type. One stored procedure for insert, another for select, update, delete, close (just set end date) for each table.
- When we must manage transaction (for insert, update, delete) we have 2 Stored Procedure for one stored Procedure. One Private and One Public.
o Public SP :
1. manage the transaction,
2. call the private SP
3. log an error if it happens and if it’s required (it depends of the error) => we check the @ERROR output param and use INS_ERRORS SP
o Private SP : do all the job : insert, update, delete. If an errors happen, concatenate all the param of the sp, give the name of the SP in 2 output param.
This schema give the possibility to reuse the directly the private SP inside another.
- We have in every SP (Public and Private SP) an output parameters called @ERROR OUTPUT. If it’s different than 0, an error happen. Easy Not ? J
- For the Private SP : we have 2 other Output Parameters @ERROR_CONTEXT NVARCHAR(2000) OUTPUT, @ERROR_METHOD OUTPUT NVARCHAR(300).
o The context parameters contains a concatenation of all the param of the SP with his value.
o The Method contains the name of the SP and a little detail where and in which SP the error happens. Because an error could happen in a SP called by another SP
These 2 info will be stored into the ERRORS_LOG table of course.
What do you think about my solution ? How did you do it ? Do you have some other solutions ? My 3 mains problems are
1. we can insert a row into a table and not be affected by the rollback after
2. The “Error Analyzer”. Is it possible in a generic way : dynamic code
3. I don’t know how catch the recordset send back by the SP that I call in another SP.
I can give you some code to give you more details. Please ask me some question to have a the full details. I know that it’s really difficult to understand the problem.
Many Thanks
Jerome.
December 16, 2002 at 11:44 am
I'm not doing anything on par with outline, but from my sp's and I had to get around the transactions and nested sp's also.
I've added a @Return_Text parameter to sp's and that's where I return System and User Messages.
If there was a system error the sp sets the @Return_Value to the @@Error and the an approriate text value to @Return_Text. The application then writes the error to a log table and presents an appropriate message to the user.
If the sp finds that there is user error, then the sp sets the @Return_Value -1 and an approriate text value is set in @Return_Text. The application doesn't write these errors to the log.
For the nested sp, the parent sp checks the @Return_Value of the child. Depending on the parent sp, the values may be passed up to the calling app, over written by the parent sp or the parent sp may evaluate the @return_value of the child and do some conditional processing.
hth
December 17, 2002 at 3:26 am
And how do you retrieve the system error message for example? Do you catch the error description raised by the system? I’m really interested to do that.
The only way that I know for the moment is the definition of the errors description in the system table master..sysmessages but it’s not really user friendly message due to the %s and so on.
If it’s possible I would like to catch the parameter passed by the system just before it raises the error. Explain in another way catch the error description that the system raise you can have it in VB) and also the parameters passed to this error description to replace %s,…
jmackels
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply