Capturing the Error Description in a stored procedure

Question

Capturing the Error Description in a stored procedure

rama-211191

Newbie

Points: 2
More actions
July 24, 2007 at 10:09 pm

#117658

Hi, I have a few stored procedure which will be executed one after another. If any error occurs, i need capture the Error number and ERROR DESCRIPTION and SAVE it into a table within stored procedure itself. Any idea how to do it???
I saw a similar problem from http://www.sqlservercentral.com/columnists/ajethva/capturingtheerrordescriptioninastoredprocedure.asp but i cannot download the sample code.
i want to CAPTURE the following msg :
e.g. Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__titleauth__au_id__0519C6AF'.
The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.
thanks.
rama

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

hi

if its system error messages you want you can put the error number (@@Error) in a variable and fetch the corresponding error description from sysmessages table. Then you can put this in a table.

The article you mentioned uses sp_altermessage to configure the errors so that the errors will be written to sql logs when they occur. For this you shud have a good idea about the errors that are most likely to occur and then you must use sp_altermessage to configure these error(s).

Hope i have helped you

"Keep Trying"

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

You can use @@error to track error messages in sql 2000 if its sql 2005 then use try and catch.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

rama-211191 Newbie Points: 2 More actions · Answer 3

Thanks for the quick reply. Sorry... As i mentioned earlier, i can see the error number only. I need to see the Error description as well. Also, I want to capture ALL the errors don't have most likely errors.

And i'm using SQL server 2000.

Hi Sugesh / anyone, possible to give a sample code... thanks in advance.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

The links below give what you want. Capturing error messages on run time. hope this will be useful for you.

http://www.sql-server-performance.com/ak_exception_handling.asp

http://www.sommarskog.se/error-handling-I.html

http://www.novicksoftware.com/TipsAndTricks/Tips-erorr-handling-in-a-stored-procedure.htm

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com