Hi everybody,
I just quickly reviewed Erland's article the section about TRY/CATCH, but I still want to understand something as we didn't get error logged properly.
Our procedures have at the very top of each one
SET NOCOUNT, XACT_ABORT ON;
Then they all have code such as
try
code here
return 0
end try
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT
@Check_Error = ERROR_NUMBER()
, @Check_Error_Message = ERROR_MESSAGE();
SET @out_Status_txt
= '**ERROR** in ' + @usp_name + ' Error Message: ' + @Check_Error_Message + ' Error code: '
+ CONVERT(VARCHAR(25), @Check_Error);
EXECUTE dbo.usp_write_audit
@inp_process_txt = @inp_process_txt
, @inp_sub_process_txt = @sub_process_txt
, @inp_type_id = 2
, @inp_action_txt = @out_Status_txt
, @inp_load_file_detail_id = 0
, @inp_job_nbr = @inp_load_report_job_nbr;
SET @out_Status_nbr = 2;
RETURN 2;
END CATCH;
One of the procedures had an error in the MERGE command (the code used wrong alias which was not used and it was giving error in run-time about unbound column). However, there is no audit information about error in that procedure, only the caller's procedure somehow trapped it.
Do you see why we didn't end up with the log for the error?
Thanks in advance.