Passing SQL Server exception to .net

Question

Stored procedure :

Procedure GetLocalConfig
(
    @ConfigId int
)
AS
SET NOCOUNT ON; 
BEGIN TRY
BEGIN TRANSACTION
SELECT 1/0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT 
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;  


     IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
        RETURN 
END CATCH

.Net code calling the Stored Procedure :

try
{
return DatabaseFactory.CreateDatabase(ConnectionStringConfigName)
       .ExecuteSprocAccessor(DbCommands.GetLocalConfig, new LocalConfigDtoMapper(), configId)
       .Single();

}
catch (Exception ex)
{
   // Exception : Sequence contains no elements???
}

If I execute this SP from SSMS I get the actual exception :

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure ErrorLine   ErrorMessage
8134        16            1           GetLocalConfig 54           Divide by zero error encountered.

I would like to see this exception in my try catch block for .Net however I keep getting Sequence contains no elements.

After some research I found I could RAISEERROR inside my stored procedure Try catch block :

DECLARE @errnum nchar(5), @errmsg nvarchar(2048);
SELECT
   @errnum = RIGHT('00000' + ERROR_NUMBER(), 5),
   @errmsg = @errnum + ' ' + ERROR_MESSAGE();
RAISERROR (@errmsg, 16, 1);

Now when I run the SP using SSMS I get :

Msg 50000, Level 16, State 1, Procedure GetLocalConfig, Line 70
8134 Divide by zero error encountered.

But still .Net is giving me the same exception, how do I get the exception to be passed over to .Net in this case?

If you suppress the exception in TSQL, how will it get back to the caller? — Jodrell
– Jodrell, Commented Jan 28, 2013 at 17:09
So the solution would be to just use Command object and ExecuteReader and then I would see the exception? — Murtaza Mandvi
– Murtaza Mandvi, Commented Jan 28, 2013 at 17:10
Even if I handle the .Single() condition with .Any() check I am still not going to see the exception. — Murtaza Mandvi
– Murtaza Mandvi, Commented Jan 28, 2013 at 17:11
Remove the Single() from you linq and see what ExecuteSprocAccessor(DbCommands.GetLocalConfig, new LocalConfigDtoMapper(), configId) actually returns. Without knowing more about your data access layer I am unable to help. — Jodrell
– Jodrell, Commented Jan 28, 2013 at 17:13
So I put a check for .Any() before the .Single() and it quietly passes the ExecuteSprocAccessor and goes to the check and returns null, no exceptions. — Murtaza Mandvi
– Murtaza Mandvi, Commented Jan 28, 2013 at 17:14

Community · Accepted Answer · 2017-05-23 11:56:02Z

1

I assume the error row you return from the GetLocalConfig SP does not map using the LocalConfigDtoMapper, its expecting a different schema.

Therfore you suppress the actual exeption, and return a row with the wrong schema. This results a zero length sequence or empty enumerable being returned to the caller. When the Single() check asserts that you have one row only, it throws the appropriate exception because you have none.

If you want to rollback on error look at these duplicates

How to rollback a transaction in TSQL when string data is truncated?

SQL Server 2008 Transaction, rollback required?

SQL Server - transactions roll back on error?

Essentialy

You can re raise the error with RAISERROR in your CATCH block. You can get the original "exception" details with the ERROR_... functions

or

SET XACT_ABORT ON

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jan 28, 2013 at 17:19

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

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

2 Comments

Murtaza Mandvi Over a year ago

I just added the answer above.Thanks !

Jodrell Over a year ago

@MurtazaMandvi, why? seems to be a reiteration of my answer.

Murtaza Mandvi · Accepted Answer · 2013-01-28 17:21:47Z

0

The TRY CATCH block inside the SP was suppressing the actual error message, removing the that I get the actual exception in my application.

answered Jan 28, 2013 at 17:21

Murtaza Mandvi

11k26 gold badges78 silver badges111 bronze badges

Collectives™ on Stack Overflow

Passing SQL Server exception to .net

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related