August 29, 2012 at 11:26 am
Hi all,
Why is it my error handling not catching the error? I purposely simulating an error trying to test logging, but there is nothing logged into the error table:
-------
Msg 3930, Level 16, State 1, Line 16
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
-------
begin
begin try
begin transaction
update fo_run_date
set fo_act_run_date = '123';-- <== this is int going into datetime field
commit transaction;
end try
begin catch
insert into perf_bench_fo_errors (proc_name, error_nbr, error_level, error_posn, error_msg, error_date)
values (
ERROR_PROCEDURE()
,ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_LINE()
,ERROR_MESSAGE()
,GETDATE()
);
rollback transaction;
end catch
end
Thanks,
Eugene
August 29, 2012 at 11:36 am
Hi,
Try this:
begin
begin try
begin transaction
update fo_run_date
set fo_act_run_date = '123';-- <== this is int going into datetime field
commit transaction;
end try
begin catch
begin try
insert into perf_bench_fo_errors (proc_name, error_nbr, error_level, error_posn, error_msg, error_date)
values (
ERROR_PROCEDURE()
,ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_LINE()
,ERROR_MESSAGE()
,GETDATE()
);
end try
begin catch
raiserror ('Error inserting into perf_bench_fo_errors',16,1)
rollback transaction;
end catch
end catch
end
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
August 29, 2012 at 12:18 pm
Thanks for reply, but ...
Nothing was logged into error table 🙁
August 29, 2012 at 3:50 pm
Eugene,
Nothing was logged into your Error table because the ROLLBACK command in the TRY block cancelled the action. Try removing it and the error will log in your table (assuming all table def's are correct, etc)
Elliott
August 29, 2012 at 3:56 pm
Elliott,
Thanks for reply, but rollback is in the CATCH block.
begin
begin try
begin transaction
update fo_run_date
set fo_act_run_date = '123';-- <== this is int going into datetime field
commit transaction;
end try
begin catch
insert into perf_bench_fo_errors (proc_name, error_nbr, error_level, error_posn, error_msg, error_date)
values (
ERROR_PROCEDURE()
,ERROR_NUMBER()
,ERROR_SEVERITY()
,ERROR_LINE()
,ERROR_MESSAGE()
,GETDATE()
);
rollback transaction;
end catch
end
Eugene
August 29, 2012 at 5:20 pm
Doh!
Yes, that's what I meant to say: Remove it from the Catch block and see what happens.
Elliott
August 29, 2012 at 5:26 pm
It didn't work. The result: the same error.
August 29, 2012 at 6:56 pm
Try it like this:
begin
begin try
begin transaction
update fo_run_date
set fo_act_run_date = '123';-- <== this is int going into datetime field
commit transaction;
end try
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
Declare @ErrorLine int;
Declare @ErrorNumber int;
Declare @ErrorProcedure nvarchar(126);
SELECT --save the error values before we RollBack
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorProcedure = ERROR_PROCEDURE();
-- RollBack *before* we write to the Log
rollback transaction;
-- Now Log it
insert into perf_bench_fo_errors (proc_name, error_nbr, error_level, error_posn, error_msg, error_date)
values (
@ErrorProcedure
,@ErrorNumber
,@ErrorSeverity
,@ErrorLine
,@ErrorMessage
,GETDATE()
);
end catch
end
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 30, 2012 at 9:51 pm
How did this work for you?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 30, 2012 at 11:03 pm
You may also want to look into XACT_STATE(). You can call this function to determine whether COMMIT will run without throwing the particular error you posted.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 31, 2012 at 5:40 am
The rollback after the error trapping is the issue. It's rolling back your error record. Move it before that insert.
RBarryYoung's code works although there's an extra "begin catch" that needs to be removed.
Mark
August 31, 2012 at 8:49 am
Mark Eckeard (8/31/2012)
The rollback after the error trapping is the issue. It's rolling back your error record. Move it before that insert.RBarryYoung's code works although there's an extra "begin catch" that needs to be removed.
Mark
Oops!
Thanks, Mark. It's fixed in the original post now ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 31, 2012 at 9:18 am
Thanks Berry and Mark,
It works now as I moved rollback to prior to the insert into error table.
August 31, 2012 at 10:09 am
glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply