Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Problem Using Try Catch ...

Author  Topic 

sqlilliterate
Starting Member

40 Posts

Posted - 2008-07-23 : 23:24:12

Since now I'am trapping my stored procedure errors at my Catch block in .Net code. But my DBA now has insisted to use Try Catch blocks in stored procedures itself...

As you would expect the exception is now not raised to .Net as it is handled by the SP itself, but I need to re-raise the error to my .Net code...

I'm helpless despite using the RAISERROR method(below)... it doesn't signal my .Net code the error... :(

begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_number()
,@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

Pls. check have I missed something, or suggest any alternate to re-raise the error to the .Net code with ignoring the Try Catch block in my Stored procedure.

thanks...

senol01
Starting Member

3 Posts

Posted - 2008-07-24 : 03:58:31
the full code you send

thanks sqlteam.com
http://www.evdenevenakliyatbul.com/
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-24 : 10:16:27
No reason your .net code should not catch your error. However its possible that the severity level for that specific error was just an "informational" error. ie severitylevel=10. That may expain it.

I would suggest identifying the exact call that raises the sql error but your .net code is missing then make that call directly in a query window to see what is raised. You can also include some intentional error(s) in your SP's TRY block to test the end to end error handling.

Finally, you should be careful raising the error in the CATCH block with the same severity level as the original error. There are internal sql errors that have a severity level that a user is not allowed to raise. If that happens your RAISERROR statement itself (in the CATCH block) will cause an unhandled sql error.

Be One with the Optimizer
TG
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-07-24 : 17:31:20
quote:
Originally posted by sqlilliterate
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_number()
,@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

I believe the problem is in red above. You are assigning the Error Number to the Error Severity variable. Error Numbers have a much wider variance in values than Severities do, and when you go to raise the error, your RAISERROR statement probably fails. You probably wanted Error_Severity() there instead.

---------------------------
EmeraldCityDomains.com
Go to Top of Page
   

- Advertisement -