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, Jan 28 '13 at 17:09
So the solution would be to just use Command object and ExecuteReader and then I would see the exception? — Murtaza Mandvi, Jan 28 '13 at 17:10
Even if I handle the .Single() condition with .Any() check I am still not going to see the exception. — Murtaza Mandvi, Jan 28 '13 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, Jan 28 '13 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, Jan 28 '13 at 17:14
got it, its the TRY CATCH which is suppressing the error as you pointed out earlier, in this case since I'm rolling back the transaction, whats the best way to Roll Back transaction if there is an exception ? (this same template for try catch is used in multiple places while doing inserts and updates where I actually need the rollback) — Murtaza Mandvi, Jan 28 '13 at 17:17

score 1 · Accepted Answer · edited May 23 '17 at 11:56

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

@MurtazaMandvi, why? seems to be a reiteration of my answer. — Jodrell, Jan 28 '13 at 17:34

score 0 · Answer 2 · answered Jan 28 '13 at 17:21

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 '13 at 17:21

Murtaza Mandvi

10,708
23
74
109

Passing SQL Server exception to .net

2 Answers2