Retrieving SQL Error Message

Question

Post reply

Retrieving SQL Error Message

Gogula G. Aryalingam

SSCrazy

Points: 2688
More actions
November 15, 2005 at 12:49 am

#92245

I am under the situation where I have to trap the exact error message returned by sql server and save it a table. Upto now I'm only able to get the error id using @@ERROR. Is there anyway that I could get the exact error message thrown by an sql statement (for example: the error message given when I try to insert a duplicate record), into a variable?

Thanks

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

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

Kory Becker Hall of Fame Points: 3565 More actions · Answer 1

Kory Becker

Hall of Fame

Points: 3565

November 15, 2005 at 7:04 am

#603898

Are you wantimg to do this from within a Stored Procedure?

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

sushila

SSC-Dedicated

Points: 35293

November 15, 2005 at 7:28 am

#603908

Hi Gogula...you could capture @@error in a variable and then use it to query the sysmessages table in the master db..something like this...

select description from master.dbo.sysmessages
where error = @errNum  --(variable that stores @@error)

**ASCII stupid question, get a stupid ANSI !!!**

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 3

Hi Guys,

Thanks for replying. Yes I want to do it from within a stored procedure. I had already thought of the method which you have suggested Sushila, but I think it would give me the error message along with the placeholders instead of the actual error which is displayed at the time of the error, i.e. with the object names and values and so forth.

There is also a function called FormatMessage which will give the error message from the sysmessages table when the error number is given. But it also expects the parameters for the placeholders.

The problem I'm facing is that the SP I'm writing should store in a table whatever error message is returned, exactly as it is returned.

Thanks again.