Error handling not working with Stored proc
-
Friday, January 18, 2013 1:15 AM
Hi, i have a syntax of proc as,
CREATE PROC procname
Begin
Begin TRY
---------code----------
End TRY
Begin CATCH
EXEC dbo.ErrorHandling
End CATCH
END
Syntax is correct but Error Handling proc doesnt load the error table
while if i simply test this, It works and loads error table.
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
EXEC dbo.spErrorHandling
END CATCHAny idea why its not working with the procedure?
All Replies
-
Friday, January 18, 2013 1:39 AMModeratorIt depends on the procedure code. Do you know if that error is trappable? Did you open a transaction inside the procedure? If so, that procedure call will participate in the transaction and if you rollback the main transaction, the rows will not be written to the error table.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Friday, January 18, 2013 1:42 AM
See this for the details
http://msdn.microsoft.com/en-us/library/ms179296%28v=SQL.105%29.aspx
Many Thanks & Best Regards, Hua Min
-
Friday, January 18, 2013 4:57 AM
Hello Skydiver01,
Please have a look at TRY...CATCH (Transact-SQL) => Remarks "A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection."
So it depends on the raised error, if try/catch works or not. See also Using TRY...CATCH in Transact-SQL for further informations.
Olaf Helper
Blog Xing -
Friday, January 18, 2013 9:31 AM
-
Friday, January 18, 2013 5:36 PM
@Naomi, How do i find out if the error is trappable? The error i am getting is like,
Msg 208, Level 16, State 1, Procedure uspCustomerUpdate, Line 46
Invalid object name 'CustomerDetails'.
I have another proc which is having trasaction. How do i log errors in that proc?
@Olaf Helper, How do i know the severity since the proc doesnt reach to catch block.
- Edited by Skydiver01 Friday, January 18, 2013 5:37 PM
-
Friday, January 18, 2013 5:53 PM
-
Friday, January 18, 2013 6:59 PM
You might find some more information about your code execution by checking XACT_STATE().
http://msdn.microsoft.com/en-us/library/ms189797.aspx
Also read:
http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
-
Friday, January 18, 2013 7:20 PMModerator
Do you get this error in run-time or when you try to save the procedure script? It looks like you're getting the deferred name resolution error which is, as far as I know, non-trappable.
Do you want to share the procedure code?
Or at least let us know if the error you get in run-time or design time and which line of code it is.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Friday, January 18, 2013 9:38 PM
@Naomi, How do i find out if the error is trappable? The error i am getting is like, Msg 208, Level 16, State 1, Procedure uspCustomerUpdate, Line 46 Invalid object name 'CustomerDetails'.
This is exactly the kind of error that I talked about in my post. Compilations that occurs at run-time like a missing table and others related to deferred name resolution are not trappable in the scope they occur. They are trappable in outer scopes. Consider this:
CREATE PROCEDURE inner_sp AS BEGIN TRY PRINT 'This prints' SELECT * FROM nosuchtable END TRY BEGIN CATCH PRINT 'This does not print' END CATCH go CREATE PROCEDURE outer_sp aS BEGIN TRY EXEC inner_sp END TRY BEGIN CATCH PRINT 'The error is: ' + error_message() END CATCH go EXEC outer_sp go DROP PROCEDURE inner_sp, outer_sp
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Friday, January 25, 2013 4:35 PM
.png)
