0

I have a stored procedure which calls 2 nested sub procedures. For Example,

MyProc
  BEGIN
    BEGIN TRY
      BEGIN TRAN
        EXEC UpdateProc
        EXEC InsertProc
      COMMIT TRAN
    END TRY
    BEGIN CATCH
      ROLLBACK TRAN
      ;THROW
    END CATCH
  END

The insert stored procedure could insert a line with duplicate values violating a unique key constraint. I'm calling the main stored procedure from a .NET application. In case of unique key violation, my application is not receiving the correct exception. The exception received is 'Specified Cast is not valid'. I've tried adding SET XACT_ABORT ON but this did not correct the problem.

  • Anyone has an idea why the unique key violation error is not bubbling up to my application ? – smmniceguy Jun 21 '16 at 7:56
0

You are missing the error handling piece (i.e. TRY...CATCH). Please see the stored procedure template that I posted in the following DBA.StackExchange answer:

Are we required to handle Transaction in C# Code as well as in stored procedure

  • Thanks for your reply.Sorry, I'm missed in the TRY-CATCH block in my question. I did used in my stored procedure. I have even tried your stored procedure template, the error is still not sent to my .net application – smmniceguy Jun 16 '16 at 9:58
  • @smmniceguy Did you apply the template to all 3 Stored Procedures? If so, then you might need to show some of that code. Are you sure that your .NET exception handling is being done correctly? The Specified Cast is not valid message could be suggesting that there is a secondary problem in the .NET code. So you might need to show that as well. – Solomon Rutzky Jun 16 '16 at 20:00
0

Did you try to catch SqlException in C# code?

Below is my example:

SQL Code:

DROP PROC IF EXISTS Proc1
GO
CREATE PROC Proc1
AS 
BEGIN
    RAISERROR('This is Proc1',0,1)
END
GO
DROP PROC IF EXISTS Proc2
GO
CREATE PROC Proc2
AS 
BEGIN
    RAISERROR('This is Proc2',18,1)
END
GO
BEGIN TRAN
    BEGIN TRY
        BEGIN TRAN
        EXEC Proc1
        EXEC Proc2
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        ;THROW
    END CATCH
ROLLBACK
GO
--DROP PROC IF EXISTS Proc1
--GO
--DROP PROC IF EXISTS Proc2
--GO

.NET code:

...
try
{
    cnn.Open();
    SqlCommand command = new SqlCommand(@"BEGIN TRY
                                            BEGIN TRAN
                                                EXEC Proc2
                                                EXEC Proc1
                                            COMMIT TRAN
                                        END TRY
                                        BEGIN CATCH
                                            ROLLBACK TRAN
                                            ; THROW
                                         END CATCH", cnn);
    command.ExecuteNonQuery();

    cnn.Close();
}
catch (SqlException ex)
{
    Console.WriteLine(ex.Errors[0].Message);
}

RESULT:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.