Capture Sql server Error

happygv · January 12th, 2007, 05:43 AM

Hi,

Has anyone tried to capture the error that SQL server throws when an sql statement results error. I know DBCC OUTPUTBUFFER holds the error in hexadecimals. But I wanted to know if anyone ever succeeded on interpretting the outputbuffer and capture the exact error that sql server returns.

Eg:

Code:

create table tbltest ( col1 int )
go

Insert tbltest values ('1abc')

The above insert statement results int he following error.

Code:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '1abc' to a column of data type int.

Here... I wish to capture
Syntax error converting the varchar value '1abc' to a column of data type int. This is what being returned by DBCC OUTPUTBUFFER as hexadecimal values.

Hope that explains.
Cheers.

_________________________
- Vijay G
Strive for Perfection

happygv · February 2nd, 2007, 04:41 AM

65 Views on this topic, but no replies?

Seems no one came across such a requirement yet. Any luck BTW? Let me know.

Cheers.

_________________________
- Vijay G
Strive for Perfection

joefawcett · February 2nd, 2007, 04:55 AM

Well it's simple in SQL Server 2005, use a try/catch block and the ERROR_MESSAGE() function. In SQL 2000 might read this but it's a lot of work http://www.aspfree.com/c/a/MS-SQL-Se...2000-and-2005/.

--

Joe (Microsoft MVP - XML)

robprell · February 28th, 2007, 10:43 PM

You can capture a SQL error within a stored procedure or within your client development tool. Am I missing the question?

happygv · March 1st, 2007, 08:00 AM

Yes rob, error handling using @@ERROR is the standard method followed in stored procedure. Client development tool is completely ruled out in this case. As my intention is to capture it from the sql server, say with a stored procedure, and get the exact error that the sql server throws in usch cases. @@ERROR results in INT value which can be used to decide if it is success or failure. I wanted to go a level above and capture the error that sql server returns from the sysmessages table using xp_printf and stuff with the % and *ls etc... replaced as shown below

Code:

Incorrect syntax near '%.*ls'.

Hope that explains.

Yes Joe, It is tough in sql server 2000, the sad thing is I wanted to accomplish this in 2000, not in 2005.

Thanks for your replies.
Cheers

_________________________
- Vijay G
Strive for Perfection

robprell · March 1st, 2007, 05:04 PM

I am mostly understanding you. Are you saying with @@error you ARE getting the int value of the error but want to know what the verbal description of that error is? If possible please describes the steps your doing up to where your not getting the results you want. Also explain what you are getting....

happygv · March 1st, 2007, 06:39 PM

As I mentioned in my post at the beginning, DBCC OUTPUTBUFFER results the error description, but in hexadecimals. I am looking for a way to parse and capture from it. It is kind of tough.

All I wanted to know is, is there a way in doing this in sql server 2000? I even found the sample shown in Joe's reply not helping.

Cheers

_________________________
- Vijay G
Strive for Perfection

robprell · March 1st, 2007, 08:08 PM

So you want to capture this??? and have the SQL server return this in a result set?:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '1abc' to a column of data type int.

happygv · March 2nd, 2007, 10:59 AM

Yes exactly... atleast the second line...

Syntax error converting the varchar value '1abc' to a column of data type int.

_________________________
- Vijay G
Strive for Perfection

robprell · March 2nd, 2007, 01:50 PM

Ok I think I understand you but I am still not sure. You can capture the description of the error and put that in a result set and return it to the calling application in a stored procedure or have the stored proc print the error message to capture that clients side. I have seen some sites that insert the error information into an error table. Is that what your trying to do?