Hi

How can we find out that there is some problem like invalida data type
or data is null or some data type errors occured when parsing the XML
in OPENXML.

Basically what I want is , Is there any way to find out in the SQL
Server 2000 stored procedures to catch exceptions that are occured
during the execution and exit the procedure and display the information
as the output or store the error log.

I tried using the @@ERROR.. but failed..

In my procedure i'm passing a very big XML as the input parameter to
the procedure. Through OPENXML i parsed the XML and storing the each
attribute values into respective tables. It is working fine.

In one of my database table there is column with data type "money" and
for testing purpose in the XML this attribute value is passed as
"character" like 100_00 instead of 100.00.

When parsing  this value through the OPENXML procedure is giving me an
error saying that "Implicit conversion is required to convert data type
varchar to data type money.".

I want to caught this error and display to client. I'm unable to catch
such type of exceptions. using @@ERROR i tried but it's always
displaying the value as 0.

So, Can any one help me out on this issue to handle such type of
unknown exceptions when executing the procedure?

(jayanth.vishnuvard***@gmail.com) writes:
Error handling on SQL 2000 is a big mess. SQL 2005 is a lot better.

Some errors you cannot catch, because the batch is terminated on the
spot. I'm not really sure that this is what is happening here. Another
ugly issue on SQL 2000 is that an error in a user-defined function
does not set @@error at all. This sounds like something similar.

If you can post a repro that demonstrates the problem, I would be
interested.

NEWSEQUINTIALID()
Slow Stored Procedure Execution
BCP error - "Unexpected EOF encountered" when using native format
Join??????
sp_recompile for user defined functions