p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Capture Sql server Error (http://p2p.wrox.com/showthread.php?t=52619)

happygv January 12th, 2007 05:43 AM

Capture Sql server Error
 
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?




All times are GMT -4. The time now is 02:55 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.