workaround for problems that cant be try caught
-
hi a peer of mine recalls an error in an edi app that often has sp 1 calling sp 2 etc etc. and in one case sp2's try catch couldnt catch certain kind(s) of error(s). below is his attempt to remember the issue. i think he is suggesting that sp1's catch couldnt provide enough helpful info either.
He wants to know if as a workaround he can count on an output param (maybe what action sp2 was on) to pass back to sp1 what sp2's try catch couldnt reveal. then the obvious question, should he forego try catch's below sp1 in favor of such a pass back feature. as i recall part of the solution was starting to use xact abort . but while that got him pretty far it wasnt the end of the story.
- This topic was modified 3 days ago by stan.
-
Is your friend using THROW along with a check on the number of transactions to force a ROLLBACK. Also, is your friend using SET XACT_ABORT ON as a setup step in the stored procedures?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
thx Jeff. He doesnt do any THROWS.
In this case he doesnt have that opportunity in sp2 before control bubbles back up to sp1.
yes he uses xaxt_abort but still at least one error doesnt get caught.
He relies on the talend job's enlistment in something like DTC (maybe it is DTC) to do rollbacks like i've seen before with ssis . But i think he is hoping that minimally if he captures the most current "app action name" in an output variable from sp2, that he can rely on sql 100% to return it to the calling sp1. he uses his own shorthand to describe each of the commands (app action name) that are about to occur and he puts them in an output variable before executing them. i'll have to ask him how if something like dtc is doing the rollback, that he is able to record permanently errors etc.
-
my peer believes this scenario has reared its ugly head again. Something tells me we addressed this once before by checking a 2nd sql built in count or flag along with tran count but i'm posting this message here anyway before i contact him for a bit of an update on what's changed over the last month or two. I'm not sure what that "o" is after ERR:. If this turns out to be us doing a wild goose chase i'll take this post down...
ERR:0. SQL: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
...and here is his pseudo code...
create or alter procedure sp1 as -- parameters omitted from pseudo-code
begin
set NOCOUNT, XACT_ABORT on
begin try
-- declare variables
-- other logic
set @whatImDoing = ‘calling sp2’
exec sp2 -- parameters omitted from pseudo-code
end try
begin catch
set @theError = ‘Error ‘ + @whatImDoing + ‘. ERR: ‘ + convert(varchar, @@ERROR) + ‘. SQL: ‘ + ERROR_MESSAGE();
end catch
end
create or alter procedure sp2 as -- parameters omitted from pseudo-code
begin
set NOCOUNT, XACT_ABORT on
begin try
-- declare variables
-- other logic
set @whatImDoing = ‘[description of step that is failing]’
-- step that fails
end try
begin catch
set @theError = ‘Error ‘ + @whatImDoing + ‘ ERR: ‘ + convert(varchar, @@ERROR) + ‘. SQL: ‘ + ERROR_MESSAGE();
end catch
end
-
I'm not sure if you're aware of this, but when you catch an error in sp2, you're effectively annulling the error, ie. it will not automatically "bubble up" to the calling sp1 (like it would, if sp2 didn't utilize a try/catch construct).
In case you want to let sp1 handle some of the repercussions of what was happening in sp2, you have to "rethow" the error again (or at least "create" an error message that sp1 can interpret and take action on).
For this you can use either THROW og RAISERROR in the catch section of sp2.
https://stackoverflow.com/questions/2481273/how-to-rethrow-the-same-exception-in-sql-server
-
thx kaj , he cant throw or raise what he cant intercept.
But either way i think what he really wants to know is for those errors that he cant/sql doesnt catch, can he rely on an output parameter (eg his own @lastaction etc) being sent back to sp1? He is reluctant to experiment with this himself until he understands what the community thinks are the rules of engagement in such cases. i think without coming right out and asking, both he and i are curious why some errors aren't caught.
i'm not sure how he concluded this but one of his employees believes a typo like updating a non existent table is one of those things that wouldn't be caught. i know you are thinking "how did the proc get added in the first place?". i'll ask.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply