SQL Server Performance Forum – Threads Archive

Catching Error Description?

Hi; I want to save Error Description which SQL Server generates when its running any query, for example Foriegn Key error when inserting, SQL server throws an Error number and Description. I can get that Error Number in the variable @@Error but how can I get that Description into variable to save in a Error Log table. I know it is saved in sysmessages table but that description has so many wild card/variable which set at the time of running query. So, how can I have the exact message in a variable which SQL throws. Actually, my goal is to save thoes errors in a table with description and calling stored Procedure, to see the log of errors. Thanks. Essa, M. Mughal
Software Engineer

The error description is stored in master.dbo.sysmessages. But note that you can not capture all error messages with SQL code. Some errors with high severity will be thrown back directly to the calling client without you beeing able to catch it in SQL. When using ADO in a an VB or ASP application you would then have to look for the errors in the ADO Error Collection.

I had the same issue some time back. I did a simple way ( May not be methodical)(
I aved those errors in to a table in a customised manner
like
@@ERROR = 547 – "A check constraint violation occurred"
212 "Expression result length exceeds the maximum."

using ado at client side can give better description of error occured.

Thanks for reply. I want to do it in Stored Procedure not at client side. I created one table for Error Log and I used Error Handler in my stored procedures and whenever I would get Error in running any Query i.e. Insert/Update/Delete then I goto Error Handler and insert the description of that Error in that table with Error Source. So I can’t go to client side to do this. I want to do it in Stored Procedure. The problem is that I have that Error Number and I can get the Description of that Error from sysmessage but that description has wildcard and variable as well, I need exact description which SQL Server Throws at client side or in the result message. thanks. Essa, M. Mughal
Software Engineer

hi Essa, M. Mughal
I think u can go for dineshasanka’s suggestion till satya dont reply to ur Q. He must be having ans for this.

Thanks Ranjit. Upon the completion of any T-SQL statement, SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designate error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

You can not get the exact error message from T-SQL. Only the text string that contain wildcards in sysmessages. Fromhttp://www.sommarskog.se/error-handling-I.html "You cannot get text of the error message, nor the severity level. (A common question on the SQL Server newsgroups, hereby answered.) And if SQL Server emits a message with a severity level of 10 or lower, SQL Server does not set @@error, and thus you cannot tell from T-SQL that the message was produced."

Hi All; Thanks for replying me, the last post answered my question, so I can’t have the text message produced by SQL Server. I think, I should go with ranjit practice, I should keep all the possible message with the same Error Number with my own written error description and using that table for complete descripted message.
Thanks everyone, this post increased my Error Handling knowledge a lot. Essa, M. Mughal
Software Engineer

]]>