Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jerome Mackels
Starting Member
4 Posts |
 Posted - 2002-12-15 : 16:51:01
|
| 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 welcomeThe actor in this design:- Errors Log Table- Business Error Reference Table- Business Error Reference Translation- Business Error- System Error- Stored Procedures- TransactionWhat are the tools that SQL Server give us :- @@ERROR- @@ROWCOUNT- Output Parameters- Stored Procedure to reuse codeTechnical 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 found3 tables :ERRORS_LOG : contains all information concerning the errors happened.ERRORS : contains only each business error NBERRORS_TL (Translation) : contains the translation of each Business Error in any existing languages1 stored procedures :INS_ERRORS : this is the stored procedure that it will be used when we want store info into the ERRORS_LOG tableStored 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 SP3. 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 SPo 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 SPThese 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 after2. The “Error Analyzer”. Is it possible in a generic way : dynamic code3. 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 ThanksJerome. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-16 : 05:21:46
|
| Several problems with your design major one.A lot of errors will cause the connection to abort or at least drop you back to the client so if you want to implement error logging it cannot be done from an SP it will have to be controlled from the client.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Jerome Mackels
Starting Member
4 Posts |
Posted - 2002-12-17 : 05:03:14
|
| Sure, all the layers must manage the errors. In fact we have an n-tiers development.Here I’m just talking about the DB part of the error. If you have connection problem, it’s sure that you can’t retrieve the info translated in the user languages. And if it’s the Server Object which manage the transaction, the design will change...And what do you think about the design? How do you do it ? |
|
|
|
|
|
|
|
|
Error Handling Design