April 21, 2009 at 1:06 pm
When a SELECT statement appears before a RAISERROR within a CATCH block, the error is not returned to the calling C# app. I coded differently to get around it, but left wondering if there is a valid reason for this behavior or if it's a documented bug?
CREATE PROCEDURE [dbo].[test_raise]
AS
BEGIN
BEGIN TRY
RAISERROR ('Test Error Raised', 16, 1)
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT,
@ErrorProcedure NVARCHAR(100)
SELECT @ErrorSeverity = ERROR_SEVERITY(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ERROR_PROCEDURE()
--SELECT 0 -- Uncommenting this causes C# to not catch this error
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber)
END CATCH
END
April 22, 2009 at 12:33 pm
It doesn't look like a SQL Server problem to me. The SP will do exactly what you tell it to which is return a zero to the calling app prior to raising the error. Your calling app is most likely taking the return value and then not waiting for the raiserror.
Just curious, why do you have the SELECT 0 there in the first place?
April 22, 2009 at 1:01 pm
Hi
I can can reproduce this. It seems that the CATCH block ends with the SELECT. Just try to add two PRINT lines above and below the "SELECT 0". The first will be returned the second won't.
Never noticed this, but you are right.
Greets
Flo
April 22, 2009 at 1:04 pm
The 'SELECT 0' was just a scaled down version of the original SELECT I had there (simplified for debugging purposes)
April 22, 2009 at 1:59 pm
Isn't 0 the successful return code in a C# process? In which case is it simply that because it gets that back, is simply doesn't look for anything else?
It looks to me that the Raise is in fact returning an error, but unless you go checking the execution status (or return status) you might not know, since it returns something....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 22, 2009 at 2:33 pm
Matt Miller (4/22/2009)
Isn't 0 the successful return code in a C# process? In which case is it simply that because it gets that back, is simply doesn't look for anything else?It looks to me that the Raise is in fact returning an error, but unless you go checking the execution status (or return status) you might not know, since it returns something....
Hi Matt
You are right, zero is the successful return code for C# processes (as console application), but it isn't as return value of a SQL statement. I also tried with -1 which is certainly no success code and still same result. I changed his error handler like below and get the "Hello World on client side but not the "Hello new world". I never tried before but seems strange...
BEGIN CATCH
PRINT 'Hello World'
SELECT -1
PRINT 'Hello new world'
END CATCH
Greets
Flo
April 22, 2009 at 2:37 pm
I just tried this:
begin try
raiserror('Error', 16, 1)
end try
begin catch
select 'First'
print 'First'
select 'Second'
print 'Second'
end catch
I got both selects and both print commands.
@@Version = Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2009 at 2:39 pm
Also tried this:
begin try
raiserror('Error', 16, 1)
end try
begin catch
select 'First'
print 'First'
select 'Second'
print 'Second'
raiserror('In Catch', 16, 1)
end catch
I get both selects, both prints, and the error message from the raiserror in the catch block.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2009 at 2:40 pm
Just to be sure, I also tried the original script, and the final raiserror worked as expected.
BEGIN TRY
RAISERROR ('Test Error Raised', 16, 1)
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT,
@ErrorProcedure NVARCHAR(100)
SELECT @ErrorSeverity = ERROR_SEVERITY(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE() + ' in Catch',
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ERROR_PROCEDURE()
--SELECT 0 -- Uncommenting this causes C# to not catch this error
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber)
END CATCH
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2009 at 2:43 pm
Hi ,
Just a wild guess, did not try reproducing it.
I think since we dont have that 'Set NoCount ON', after every select the message about the number of rows that is returned is going back to the c# part overtaking the error?
---------------------------------------------------------------------------------
April 22, 2009 at 2:47 pm
Pakki (4/22/2009)
Hi ,Just a wild guess, did not try reproducing it.
I think since we dont have that 'Set NoCount ON', after every select the message about the number of rows that is returned is going back to the c# part overtaking the error?
That's pretty common.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 22, 2009 at 2:50 pm
Hi Gus (hope I am allowed to call you Gus)
I just tried again and I think I figured out the problem. It depends on the C# code. As long as I use a DataTable to be filled .Net ends after the first returned data (the SELECT). When I use a DataSet which is made to handle more than one result set I get all data, the PRINT messages and the RAISERROR, if defined.
Greets
Flo
April 22, 2009 at 2:52 pm
Really wierd.
I tired to reproduce the issue on my SQL Express box and the first time it worked (I mean I was able to reproduce the problem).
Now I can not anymore.
I will try on my box at home.
Might be some really crazy bug.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 22, 2009 at 3:00 pm
Hi SSCertifiable,
Just to make sure we're on the same page, you ran the final one with the '--SELECT 0' uncommented? and you still got the error in C#?
April 22, 2009 at 4:11 pm
If you run the script exactly as you have it, the RAISERROR works because you're doing a variable assignment, not a select that returns a resultset.
If you uncomment the actual SELECT statement (select anything you want), the RAISERROR at the end of the t-sql catch block does not make it back, at least to a C# catch construct.
It seems that a SELECT (the kind that returns a resultset) just "outputs" the resultset and then stops.
-George
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply