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.
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 catchPls. 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 sendthanks sqlteam.comhttp://www.evdenevenakliyatbul.com/ |
 |
|
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 OptimizerTG |
 |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-07-24 : 17:31:20
|
quote: Originally posted by sqlilliteratebegin 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 |
 |
|
|
|
|
|
|