| Author |
Topic |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-10-27 : 04:22:53
|
| How can I write a generalized error handler.right now iam thinking of capturing @store_proc_name,@table_name,@stored_proc_paramerters and @error_description in to a error_handler table...So that any kind of error can be saved in a table and worked on separately...Will these information help us in finding out what type of error has occurred where ?..... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-27 : 04:25:54
|
| See my comment on the other tghread.If you write this fom the client then yes. If from the erroring batch then you will miss errors.Also the error description is not easily available seehttp://www.nigelrivett.net/spFormatOutputBuffer.html==========================================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. |
 |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-27 : 04:26:37
|
| inside one stored procedure they may have multiple insert and update statements as well....Transactions also needed to be builded so that if any error happens it should rollback and capture the error |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-27 : 04:54:08
|
| Again it is not always possible as the error may abort the batch/connection.If you want it to be reliable then it has to be done 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. |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-27 : 12:13:22
|
| I was thinking of capturing the error like thisCreate procedure dbo.usp_error_capture@stored_proc varchar(100),@table_name varchar(100),@stored_proc_parameters(7000),@error_description varchar(7000)asInsert into tbl_error_capture(stored_proc,table_name,stored_proc_parameters,error_description)values(@stored_proc,@table_name,@stored_proc_parameters,@error_description)By this I will know which stored procedure gives error,what table and what are the stored-proc parametersthat is being passed and the error_description...Is this the way I should go ahead |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-27 : 12:54:06
|
| Will this be enough for knowing what error has occured |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-27 : 13:32:24
|
| please somebody give me a tip on this |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-27 : 13:34:49
|
| Did you see Nigel's last comment about not being about to call all of the errors from SQL Server unless you do it at the client? Some errors cause the batch to be aborted, therefore you can't capture it inside a stored procedure. But you can catch it on the client side.Tara |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2004-10-27 : 13:38:12
|
| Yeah i am catching at the client side..So this error capture will be implemented accross all the stored procedures so that any error while inserting to a table or updating or deleting can be captured |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-27 : 14:31:19
|
| How do you know which table the error was on?When I do this sort of thing I capture the SP name start time, end time, parameters, user, try to guess which error will be the main one and then in another table save all the errors associated with the call (there will be more than one - usually a generic failure, a specific error and maybe others. Depending on where the error occurs it's not easy to tell which will be the failure cause).Sometimes I will concatenate the errors together.Also consider whether it is a good idea to save it to a table - if the problem was with the connection then you masy not be able to save the error so consider a text file on the local m/c too.I assume you are implementing a dal so all calls are via a single module.==========================================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. |
|
|
|
|
error capture issue