Sql Exception Not Being Passed to C# Catch

Question

I have an automated script written in C# that runs a stored procedure on SQL Server 2014. The stored procedure is running multiple select, update, and insert statements and utilizes a try catch rollback pattern to catch and rollback the entire transaction when there's an exception.

It looks similar to this:

BEGIN TRY
    BEGIN TRANSACTION TransName
    --Lots of SQL!
    COMMIT TRANSACTION TransName
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TransName;
    THROW
END CATCH

My C# that calls the procedure looks similar to this:

using (SqlCommand Command = new SqlCommand(query, Connection))
{
    // Retry several times if the query fails.
    for (var retry = 0; retry < 5 && !Success; ++retry)
    {
        try
        {
            Command.ExecuteNonQuery();
            Success = true;
        }
        catch (SqlException e)
        {
            // Handling for Timeout or deadlocks.
            // If not a timeout or deadlock and retry hasn't happened 4 times already.
            if (!(e.Number == 1205 || e.Number == 1204 || e.Number == -2) || retry == 4)
            {
                LogException(e);
            }
            else if (e.Number == 1205 || e.Number == 1204)
            {
                // Wait to avoid hammering the database.
                Thread.Sleep(500);
            }
            else if (e.Number == -2)
            {
                // Wait to avoid hammering the database.
                Thread.Sleep(5000);
            }

            Success = false;
        }
    }
}

I have it looping to make sure the SQL goes through if there is a deadlock or timeout since it's an automated script.

In my logs for the script I can see that the stored procedure did not log any exceptions, but none of the data exists in the tables that the procedure touches which brings me to my question:

Is it possible for an exception to be caught in T-SQL and then thrown again using a T-SQL THROW statement but then the exception is not thrown in a C# client?

Let me know if I can clarify anything. Thanks!

Mad Myche · Accepted Answer · 2017-05-03 14:59:40Z

3

The try...catch in SQL works a little differently, what I have done in the past is to use OUTPUT variables on the stored procedure:

ALTER PROCEDURE dbo.yourStoredProcedure
    (-- your parameters
     @errNumber  INT OUTPUT,
     @errLine    INT OUTPUT,
     @errMessage VARCHAR(MAX) OUTPUT)
AS
BEGIN

    SET @errNumber  = 0
    SET @errLine    = 0
    SET @errMessage = ''

    BEGIN TRY
        BEGIN TRANSACTION TransName
        --Lots of SQL!
        COMMIT TRANSACTION TransName
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION TransName;

        SELECT   @errNumber  = ERROR_NUMBER()
        ,        @errLine    = ERROR_LINE()
        ,        @errMessage = ERROR_MESSAGE()
    END CATCH   
END
GO

And you would need to adjust the try within your C# to add the parameters and read the return values

    try
    {
        SqlParameter errNumber = new SqlParameter("@errNumber", 0);
        SqlParameter errLine = new SqlParameter("@errLine", 0);
        SqlParameter errMessage = new SqlParameter("@errMessage", "");

        Command.ExecuteNonQuery();

        int SqlError = (int)(errNumber.Value);
        int SqlLine = (int)(errNumber.Value);
        string SqlMessage = (string)errMessage.Value;

        if (SqlError == 0 ) { Success = true; }
        else {
            Success = false;
            // whatever else you want to do with the error data
        }
    }

Your SqlException catch would still catch the errors that were not within the procedures TRY...CATCH, and you should also have a generic Catch(Exception ex) block as well for other errors and finally don't forget the finally {} for any cleanup that may be needed.

Update 05/03/2017

In most cases, wrapping a transaction within a try...catch leads to uncommitable transactions. So we can flip the wrapping to have the try-catch within the transaction. If an error is caught then we should be able to get the error values and if again a transaction exists (@@transcount >0) it will be rolled back and @@transcount would be reduced to 0. After the the try-catch block is closed we again check @@transount and commit if one exists

BEGIN TRANSACTION TransName

BEGIN TRY
    --Lots of SQL!
END TRY

BEGIN CATCH
    SELECT   @errNumber  = ERROR_NUMBER()
    ,        @errLine    = ERROR_LINE()
    ,        @errMessage = ERROR_MESSAGE()

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION TransName
END CATCH

IF (@@TRANCOUNT > 0) COMMIT TRANSACTION TransName

edited May 3, 2017 at 14:59

answered May 1, 2017 at 22:48

Mad Myche

1,0751 gold badge7 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shmoman Over a year ago

I have a generic Catch(Exception ex) that this is covered by later in the code, and nothing has been logged from those either. I will try the Output variables and see if anything comes up...

Shmoman Over a year ago

No luck, I added the output variables, but nothing is being returned by them, that tells me that the error is not with the try catch, but something else is rolling back the changes.

Mad Myche Over a year ago

Updated procedure

Shmoman Over a year ago

Setting SET XACT_ABORT ON; at the beginning of the proc solved the issue. See my answer for more details. Thank you for your answer though! it helped me rule out other issues I thought it could have been.

Paul · Accepted Answer · 2019-03-25 19:01:45Z

2

This is covered in this blog: http://www.dbdelta.com/the-curious-case-of-undetected-sql-exceptions/

ExecuteScalar will not raise an exception if a T-SQL error occurs after the first row is returned. Also, if no rows are returned because the row-returning statement erred and the error was caught in T-SQL, ExecuteScalar returns a null object without raising an exception.

The same issue can happen with ExecuteNonQuery for the same reason.

answered Mar 25, 2019 at 19:01

Paul

211 bronze badge

Comments

shurik · Accepted Answer · 2017-05-01 22:43:28Z

0

Do you catch and log other exception types somewhere down the road? What happens if something other than SqlException is thrown? Is it logged?

Regarding retry logic - I would also handle InvalidOperationException. ExecuteNonQuery will throw InvalidOperationException if Connection is not open. For example a connection may go into ConnectionState.Broken state due to a brief network outage or something. Similar to how you retry for deadlocks and timeouts I would catch InvalidOperationException, check connection state and if it is not open - reopen it and retry.

answered May 1, 2017 at 22:43

shurik

81510 silver badges19 bronze badges

2 Comments

Shmoman Over a year ago

The code above is contained within a method that helps facilitate running sql queries. When that method is called it is wrapped in a try{} catch(Exception ex){} that logs anything else that could have been thrown, so I know there aren't any other exceptions being caught by the c# code. I will look into the InvalidOperationException through. Thanks!

shurik Over a year ago

Makes sense, in this case I do not know why nothing gets logged. THROW in the catch block does re-throw the exception (or at least I am not aware of situations where it would not :) ). Handling InvalidOperationException potentially should make the code more resilient but it still does not explain why you do not have anything in the log. Possibly something outside of code snippets you provided is causing the issue

Shmoman · Accepted Answer · 2017-05-04 20:48:50Z

First off, thank you @MadMyche for your suggestion, adding the output parameters helped me know that the catch was never getting hit for some reason.

What was happening is that when the query was running it would occasionally timeout and get returned back into the retry loop in the C# code, when this would happen the transaction that had been opened in the query wasn't getting closed. When the retry loop would finally loop back around and finish the query successfully it would then close the sql connection and when that happens the SQL engine goes through and closes and does a rollback on any open transactions, which was removing the data that had been saved.

I found this article that explains what happens and offered a solution:

http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx

Then I found another article that re-enforced the solution:

http://www.sommarskog.se/error_handling/Part1.html

What I did to solve it was setting XACT_ABORT at the beginning of the proc:

SET XACT_ABORT ON;

Setting XACT_ABORT "Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error" (See documentation)

With XACT_ABORT set to ON the transaction will rollback before the connection can be closed by the c# client so that there aren't any open transactions to be rolled back.

Collectives™ on Stack Overflow

Sql Exception Not Being Passed to C# Catch

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related