none
Is it true that the C# Data Access code will get exception when @@ERROR<>0? RRS feed

  • Question

  •  

    In our stored procedures, some staffs create output parameter @return_value, and set the @return_value to @@ERROR. And check the output parameter in C# data access code, but I think it's not necessary because the application will get exception when @@ERROR is not equal to 0. Am I right?

    Wednesday, October 31, 2007 2:21 PM

Answers

  • Then you will have to put the

    SET XACT_ABORT ON


    in your proc code to make the procedure fail also at statement level errors. This will eliminate the need to put error handling after every statement. According to transactions, it depends where you do your transaction handling.


    "My question is, when @@error<>0, the C# code should get exception, is it true? Is there any cases that the @@error<>0, but the C# will not get exception? Is it necessary to check the return value in C# code and throw exception if return value is not 0?"

    Well, yes and no. Error with a severity of <= 10 will have an error message and number but are not sever enough to come to the C# stack as an exception.it really depends on how you want to proceed with errors. Did you check the new TRY CATCH syntax in SQL Server 2005 ?

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Thursday, November 1, 2007 12:41 PM

All replies

  • That depends in your exception strategy, if you are catching the errors you probably can have an @@error != 0 but the code still exits successfully. In addition there can be errors which will not cause the batch to abort.

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Wednesday, October 31, 2007 4:50 PM
  • Hi,

     

    What I want is that if there is any error in stored procedure, the C# application should get the exception. We'll have proper exception handling code.

     

    In stored procedures, we'll check @@error after insert/update/delete/select into etc, if @@error <>0, will return immediately with the error code.

     

    My question is, when @@error<>0, the C# code should get exception, is it true? Is there any cases that the @@error<>0, but the C# will not get exception? Is it necessary to check the return value in C# code and throw exception if return value is not 0?

     

     

     

    Thursday, November 1, 2007 6:03 AM
  • Then you will have to put the

    SET XACT_ABORT ON


    in your proc code to make the procedure fail also at statement level errors. This will eliminate the need to put error handling after every statement. According to transactions, it depends where you do your transaction handling.


    "My question is, when @@error<>0, the C# code should get exception, is it true? Is there any cases that the @@error<>0, but the C# will not get exception? Is it necessary to check the return value in C# code and throw exception if return value is not 0?"

    Well, yes and no. Error with a severity of <= 10 will have an error message and number but are not sever enough to come to the C# stack as an exception.it really depends on how you want to proceed with errors. Did you check the new TRY CATCH syntax in SQL Server 2005 ?

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Thursday, November 1, 2007 12:41 PM
  • Hi, your message is helpful.

     

    What I want is to terminate the statement if there is any error, and handle the error in C# application. Thus the SET XACT_ABORT ON is good, no need to check @@error everywhere. I'll try it.

     

    The SET XACT_ABORT ON will effective in stored procedure level or connection level? Will it be reseted after the procedure finished?

     

    You said the severity<=10 will not cause SQL Server to throw exceptions to client application, but the error number @@error will <>0, right? Is this kind of error can be ignored? Is it necessary to handle it in C# application? Will it stop if SET XACT_ABORT ON?

     

    I know there is TRY CATCH in SQL 2005, but we haven't tried yet because there is some limitations.

     

    Friday, November 2, 2007 1:37 AM
  • "The SET XACT_ABORT ON will effective in stored procedure level or connection level? Will it be reseted after the procedure finished?"

    Well, this is done on a connection level (and sure therefore within th stored procedure). You can change the default behaviour changing the server settings. if only done within the procedure, it will be resetted after that scope.


    "You said the severity<=10 will not cause SQL Server to throw exceptions to client application, but the error number @@error will <>0, right? Is this kind of error can be ignored? Is it necessary to handle it in C# application? Will it stop if SET XACT_ABORT ON?"

    Well, that depends on your exception handling strategy and how interesting the Errors are you are sending to the client. If you want to use this feature, you will have to turn this on at the connection level (Using the property FireInfoMessageEventOnUserErrors and use link to the event InfoMessageon the connection instance to consume these information)

     

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Friday, November 2, 2007 7:55 AM
  •  

    Hi, thanks for the information.

     

    I think our application doesn't care about those "information" from SQL Server, we only cares about the exceptions, that causes users can't continue, or those business exceptions by RAISEERROR.

     

    Our policy is to control the exceptions and transactions in C# code, instead of in stored procedure. So, in our stored procedure, we'll check @@error (or use SET XACT_ABORT ON) to let the statement with error stop immediately.

     

    But, from your answers, the @@error seems not so reliable because the "information" messages may set it to non-zero, and the C# application can't get the exception.

     

    So, what's the best practice? Will "SET XACT_ABORT ON" be good enough to meet our policy?

     

    Friday, November 2, 2007 8:15 AM
  • Hi.

     

    Did you find out how to do this the best way? I want to do almost the exact same thing as you and have the same questions as you raised here.

     

    Thankyou!

    Monday, November 26, 2007 10:26 AM
  • We use SET XACT_ABORT ON in our stored procedure.

    Tuesday, November 27, 2007 1:55 AM
  • Sorry for coming back a little bit late, but I get no more notifications when a thread was edited.

     

    The statement should fit your needs.

    Jens K. Suessmeyer

    ---
    http://www.sqlserver2005.de
    ---

    Wednesday, November 28, 2007 8:03 AM