September 13, 2008 at 7:07 pm
Hi all,
According to the BOL page "TRY...CATCH (Transact-SQL) ", it states, "When the code in the CATCH block completes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application."
This seems not to be the case.
I want to catch an error, send an email describing it, then continue processing as though nothing happened. However, the errors persist and show up in an activity logging event that comes later in the process.
Does anyone know how to reset the error state to zero without stopping the process?
TIA
September 13, 2008 at 11:20 pm
Is the error not getting caught at all or is the error state not being cleared? There are unfortunately quite a few errors that end up being "batch-terminating". They can be caught, but only by a try catch construct in a higher context. Meaning - if you were to encapsulate that into a proc or an exec statement, and wrap a call to said proc or EXEC, you'd catch the error.
The second thing I've on occasion resorted to is to make sure the error state is reset by issuing something I know will not fail via a select statement. Execute that within the CATCH statement. Since each DDL/DML call resets the error state, voila - no error.
----------------------------------------------------------------------------------
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?
September 14, 2008 at 12:11 am
The error is being caught properly, but the error state is not being reset for the error_message() and error_line() functions. They continue to return stuff and it messes up the activity logs making the analyst think something is broken.
Code snippet.
begin try
set @activity = 'Execute some code on a remote server'
execute ( @sql_string )
end try
begin catch
-- DTC service is off on one server which throws:
-- error number 8501
-- error message "MSDTC on server 'server_name' is unavailable."
-- email sends correctly with the error message and number in the text
select
@subject = @procedure_name + ' Errored on [' + @server_name + ']'
,@body = 'Stored Procedure: ' + upper( coalesce( @procedure_name, 'NULL' ) )
+ char(13) + 'Activity: ' + ERROR_MESSAGE()
+ char(13) + 'Time: ' + convert( varchar, current_timestamp, 121 )
+ char(13) + 'Error code: ' + coalesce( cast( ERROR_NUMBER() as varchar ), 'NULL' )
EXEC dbo.usp_util_send_cdosysmail
@transaction_id
,@from = @from
,@to = @to
,@subject = @subject
,@body = @body
end catch
-- a little later on...
begin try
select @activity = 'Insert some table'
insert into dbo.table_name(
several_columns...
)
select
several_columns...
from##_temp_table
select @Row_Count = @@rowcount, @table_name = 'table_name'
-- This procedure hits the error functions (number, message, line, etc...) internally
-- and they return values from the remote procedure call (above)
-- and go into log records for later activity.
exec dbo.usp_util_activity_logging
@transaction_id= @transaction_id
,@procedure_name= @procedure_name
,@table_name= @table_name
,@activity= @activity
,@dml_type= 'I'
,@row_count= @Row_Count
end try
begin catch
error_handling
end catch
-- The final logging activity occurs a little later on...
-- MSDTC error string is still being returned by error_message()
-- So, I have two log records with a whacky error in them
-- that shouldn't be there.
set @activity = 'END________' + upper( @procedure_name )
exec dbo.usp_util_activity_logging
@transaction_id = @transaction_id
,@procedure_name = @procedure_name
,@activity = @activity
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply