Untrappable Errrors
-
Hi,
I'm trying to trap batch fail errors doing the following. I found this example in a thread from 2006.
__________________________
CREATE PROCEDURE philproc
as
DECLARE @error int
SELECT * FROM #fred -- Induces an untrappable error (208)
-- as #fred does not exist
-- Execution flow returns to the calling code
SELECT @error = @@error -- this line is never executed, and @error is left undefined
PRINT 'Point 1' -- this line is never executed
IF @error <> 0 -- These lines are never executed; RETURN status is undefined
RETURN @error
ELSE
RETURN 0
go
-- Run the above proc.
DECLARE @error int, @ret_stat int
EXEC @ret_stat = philproc -- SP fails with untrappable error; @ret_stat is undefined.
-- Batch does not terminate, but continues to the next line.
SELECT @error = @@error -- @error is set to the value of the untrappable error within the SP.
SELECT @ret_stat
SELECT @error
____________________________________________________
Now this code works, I've tested it. When I apply the same logic to my code I'm not getting the @@Error of the failed stored proc.
Are there any system settings or general settings that would mean this wouldn't work?
My code doesn't do the code in the example proc that traps errors (as it's never executed) My code looks like this.
EXEC @Ret_State = Internet_DailyActivity2Unmatched
SELECT @Error = @@Error
SELECT @Ret_state
SELECT @Error
I get 0 returned for the @Error value, and -6 returned for the Ret_state value. The error I want to handle is
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Any help would be greatly appreciated.
Cheers
C
-
Because procedure contains temp table it's recompiled every time it's executed.
On recompilation optimiser checks for existence of all objects mentioned in SP.
Because #fred does not exist and is not created inside of SP compilation fails.
It never comes to execution of the code, so there is no chance for any traps in the code to work.
Your code is never executed.
_____________
Code for TallyGenerator -
Actually, the stored procedure is being executed, it fails because the table does not exist. Here is what I did to get your code to work:
ALTER PROCEDURE dbo.philproc
as
begin
DECLARE @error int
PRINT 'In stored Proc'
if not exists(select 1 from tempdb.sys.objects where name = '#fred')
begin
print 'Does not exist'
SELECT @error = @@error -- this line is never executed, and @error is left undefined, but it sets it to 0.
-- you need to put your own error code here, example: set @error = -1 -- table does not exist
end
else begin
SELECT * FROM #fred -- Induces an untrappable error (208)
-- as #fred does not exist
-- Execution flow returns to the calling code
SELECT @error = @@error -- this line is never executed, and @error is left undefined
end
PRINT 'Point 1' -- this line is never executed
IF @error <> 0 -- These lines are never executed; RETURN status is undefined
RETURN @error
ELSE
RETURN 0
end
go
-- Run the above proc.
DECLARE @error int, @ret_stat int
EXEC @ret_stat = philproc -- SP fails with untrappable error; @ret_stat is undefined.
-- Batch does not terminate, but continues to the next line.
SELECT @error = @@error -- @error is set to the value of the untrappable error within the SP.
SELECT @ret_stat
SELECT @error
-
Hi There,
Phew finally got this sorted. I have another interesting problem that I would appreciate some help with. It's under the post "Dynamic Error Trapping" and it's a beauty.
Cheers
C
-
Lynn,
Let's do some exercise.
Start Profiler, open Trace Properties, add all "SP" events to selected events list and start the trace.
Now execute procedure.
Find "SP: Recompile" event right after |SP:StmtStarting|SELECT * FROM #Table| which effectively ends SP execution.
No code from procedure is executed after it.
Procedure has failed during recompilation.
Recompilation errors cannot be captured by internal code by definition.
_____________
Code for TallyGenerator -
Craig,
You mean you fixed your problem? Would you mind posting the solution? This is a forum, after all
--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) -
Sergiy,
No need. I put a PRINT statement just before the select, ALTERed the proc (which ran successfully), then executed the proc, and the print statement worked THEN the proc failed. Therefore, the proc was compiled successfully, and then failed. Adding the test for existance for the temp table then allowed the statement to skipped if the table did not exist.
Also, if this proc was called from within another procedure where the table table was created, it would work.
Lynn
-
Lynn,
There is need.
You just don't know how it works.
So, do the exercise and learn something.
SP will be recompiled every time it reaches the point where temp table is referenced.
If you include SELECT * FROM #Table in SP code 5 times it will be recompiled 5 times.
And you "PRINT" thing does not change the fact that error did not happened during code execution, the executable code did not exist at the moment because it was being compiled when error happened.
That's why no error trapping in the code could help here.
_____________
Code for TallyGenerator -
Sergiy,
I am not going to debate the issue. There is a fix to the problem encountered by the OP, and it is to test for the existence of the table before trying to select from it. If you leave that out, the procedure fails on the line containing the SELECT statement, and therefore the procedure had to start running for it to reach that point in the procedure.
Lynn
-
I know.
There is nothing to debate.
It's just impossible for you to admit you're wrong or did not know something.
No more issues here.
_____________
Code for TallyGenerator -
By the way,
Craig, don't use
if not exists(select 1 from tempdb.sys.objects where name = '#fred')
Not every user in every environment could have permissions to query system tables in system databases.
Not to mention that actual name of table "#fred" is not '#fred', so it just won't work.
Use
IF Object_ID('tempdb..#fred') IS NOT NULL
instead.
_____________
Code for TallyGenerator -
Hi,
I've not explained myself properly. The code is designed to fail so that I can test how to trap a batch failure when the sp fails. If I add the if @Error 0 RETURN statement into my code (even though it batch fails and doesn't get executed) I get the correct response when it returns to the calling code.
Sorry to have confused everyone on this.
Cheers
C
-
Craig,
as I said, it's not a run-time error.
If in any .Net language you call a method which is not declared - can you catch this error?
No, such code will fail to be compiled. It will never come to Try...Catch section.
Same story here.
The only difference is that in T-SQL compilation happens in line with execution.
It causes some confusion for many people not familiar with T-SQL.
But if you separate compilation errors from run-time ones the picture will become absolutely clear.
_____________
Code for TallyGenerator -
>> IF Object_ID('tempdb..#fred') IS NOT NULL
This is where I will agree with you Sergiy. Using the OBJECT_ID system function is better than using the system table directly. I will admit I couldn't remember the syntax for using it with a temporary table at the time I was writing my code.
-
And, if you want to make sure it's a table...
IF Object_ID('tempdb..#fred','U') IS NOT NULL
--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)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply