RAISERROR within CATCH not propagated to C# Caller

  • I have generic error logging SP that I call from SPs CATCH block to log the error and then ReRaise the error for the caller to catch. It works SP to SP. But the calling C# code does not get the exception.

    After a lot of tests, I have realized that if one does RAISERROR in the SP code (with out TRY/CATCH), it works, but if RAISERROR is done from within a CATCH block it doesn't!

    Why? Is this a SQL 2008 Bug? Any suggested workarounds?

    Here are my 2 test Stored Procs to demo the problem:-

    Calling spCauseError0 from C# - no exception is thrown back to the code

    Calling spCauseError1 from C# works (an exception is thrown back to the code)

    ALTER PROCEDURE [dbo].[spCauseError0]

    AS

    BEGIN

    DECLARE @ErrorMessage NVARCHAR(512)='';

    BEGIN TRY

    SELECT 1/0 -- Cause an error

    END TRY

    BEGIN CATCH

    RAISERROR( 'An Error has occurred in my SP!', 16, 1 );

    END CATCH

    END

    ALTER PROCEDURE [dbo].[spCauseError1]

    AS

    BEGIN

    RAISERROR( 'An Error has occurred in my SP!', 16, 1 );

    END

  • Stewart,

    Can you provide a short C# script please - I'd like to see that side of the code too. Also, it would help to know which version and build of SQL Server (e.g. SQL Server 2008 build 10.0.4272) you are using, as well as which driver you are using to connect (e.g. Native Client, OLEDB, ODBC). If you can provide a repro that closely matches you real scenario without being 500 lines long, that will save a lot of time and potential wrong turns.

    Thanks,

    Paul

  • Hi Paul,

    I am using SQL Server 2008 (+SP2) 10.0.4000 on Windows 7 (OS=6.1.7600)

    The dev env is VS2010 and I have .Net4 configured for the application.

    The connection string AppConstants.Instance.strSEIFSAConnectionString (from web.config) is

    connectionString="Data Source=STEWART-PC\SQLSERVER;Initial Catalog=SeifsaCRMX;Integrated Security=True" providerName="System.Data.SqlClient"

    // Sample C# to Demo The issue....

    // In summary if I use spCauseError1, the exception is caught here and my LogException code is executed.

    // In summary if I use spCauseError0, the code executes "silently" - no exception thrown.

    public void DoCauseError()

    {

    using (SqlConnection sqlConnection = new SqlConnection(AppConstants.Instance.strSEIFSAConnectionString))

    {

    using (SqlCommand sqlCommand = new SqlCommand("spCauseError", sqlConnection))

    {

    sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

    try

    {

    sqlConnection.Open();

    sqlCommand.ExecuteScalar();

    }

    catch (SqlException ex)

    {

    LogExceptions.Instance.LogException(0, 0, 0, 0, 0, "CommonVM:DoCauseError SqlException", ex);

    throw;

    }

    catch (Exception ex)

    {

    LogExceptions.Instance.LogException(0, 0, 0, 0, 0, "CommonVM:DoCauseError", ex);

    throw;

    }

    finally

    {

    sqlConnection.Close();

    }

    }

    }

    }

    Thanks, Stewart

  • Ah right - I see it now. The SELECT 1/0 returns an empty result set. Execute Scalar only opens the first result, so your code never encounters the error in the second result. Change SELECT 1/0 to DECLARE @S INT; SET @S = 1/0; to see the difference. Avoid returning any result sets from the procedure before calling Execute Scalar, or use Execute Reader and call Reader.NextResult to process the packet with the error inside.

    Paul

  • Hi Paul,

    I understand your point about ExecuteScalar() will only "see" one result set, so indeed if my SP contains:-

    BEGIN TRY

    DECLARE @S INT;

    SET @S = 1/0;-- Cause an error

    END TRY

    BEGIN CATCH

    RAISERROR( 'An Error has occurred in my SP!', 16, 1 );

    END CATCH

    The sqlCommand.ExecuteScalar(); experiences the exception!

    Now of course the bigger picture is that my try/catch block in the SP is to catch any SQL error, log the exception, and then report it back to the C# caller in the form of an exception.

    So if the caller is using ExecuteScalar(), the exception is not seen, as you have pointed out.

    So for this be general, as I really want the caller to get an exception for "any" sql error, which includes a SELECT1/0; it strikes my that my CATCH block should clear any possible result sets so that the RAISERROR is the only result? I have done some googling but I don't see how to clear the result set - is it possible?

    Thanks, Stewart

  • stewart.gadget (2/27/2011)


    I have done some googling but I don't see how to clear the result set - is it possible?

    I'm afraid not. If something else in the procedure (or caused by a statement in the procedure - e.g. a trigger) has already generated a result set, it is not possible to 'clear the output buffer'. Sorry about that, you'll need to design the code differently.

  • Gadgetman Stewart (2/27/2011)


    Hi Paul,

    I understand your point about ExecuteScalar() will only "see" one result set, so indeed if my SP contains:-

    BEGIN TRY

    DECLARE @S INT;

    SET @S = 1/0;-- Cause an error

    END TRY

    BEGIN CATCH

    RAISERROR( 'An Error has occurred in my SP!', 16, 1 );

    END CATCH

    The sqlCommand.ExecuteScalar(); experiences the exception!

    Now of course the bigger picture is that my try/catch block in the SP is to catch any SQL error, log the exception, and then report it back to the C# caller in the form of an exception.

    So if the caller is using ExecuteScalar(), the exception is not seen, as you have pointed out.

    So for this be general, as I really want the caller to get an exception for "any" sql error, which includes a SELECT1/0; it strikes my that my CATCH block should clear any possible result sets so that the RAISERROR is the only result? I have done some googling but I don't see how to clear the result set - is it possible?

    Thanks, Stewart

    You could always save the result sets in a table until the very end for final retrieval.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply