Trapping errors from underlying stored procedure in case of fatal-error
-
Hi,
I am calling a stored proc (P1) from another stored proc(P2). The underlying proc (P1) is trying to retrieve data from a non-exiating table. I would like to trap this error from the underlying sp and take some action from the caller sp(P2). But, the sp (P1)aborts immediately; SET @SQLError = @@ERROR statement is not getting executed.
Is there way to trap this kind of errors in SQL Server 2000
Thanks,
Suresh
-
No, not really.
Some errors in T-SQL are fatal to the batch and will abort it immediately, and this is one of them. It's not possible to trap these in T-SQL, you'll have to handle those at the calling (client) end.
However, if you anticipate that you'll risk doing operations against nonexisting objects, you can work around the fatal error by first checking if the object in question exists before attempting to access it. That way the fatal error never happens, but you can still do actions against the knowledge of that object not existing.
if (object_id(dbo.myTable) is null)
begin
do some stuff cause object doesn't exist
else
it's there, so go ahead and access it
end
/Kenneth
-
Thanks Kenneth for your reply..
I am rellay facing problem under situations where I have a temp table with some data. I am trying to create an Unique Clustered Index on a particular column expecting that the data is unique by that column. The index creation fails due to the presnce of duplicates. In such a case, it is difficult to predict the issue at design time. Is there any way to propagate the error to higher level calling procedure.
Thanks,
Suresh
-
Your case is easy.
You just need to run some checks before creating an index.
SELECT <List of Columns for proposed Index>
FROM <Table Name>
GROUP BY <List of Columns for proposed Index>
Having COUNT(*) > 1
IF @@Rowcount > 0
print 'There are duplicates, don't create unique index!'
ELSE
CREATE UNOQUE INDEX ....
But if you need to check if remote server is not accessible because of recent changes in firewall settings...
That's a real problem.
_____________
Code for TallyGenerator -
Sergiy got it right. You have to turn around your thinking.
Instead of attempting a (possibly fatal) operation and only after the fact evaluate if it did or did not work, do it the other way around.
Ask yourself - can I do this? (eg create a unique index) Well, I know I can't have dupes, else it will fail, and using the create index statement as a 'checker' doesn't work too well, since the connection dies on you if it fails. So, as Sergiy suggests, find out if there are any obstacles in the way first, if there are, decide what to do (like removing all dupes or similar), then go ahead with the creation of your index.
This is very similar to standard datascrubbing, so it's nothing strange about it. The exact details depends on your particular situation, but the general approach is the same - when in doubt, ask first, then decide what to do. After all questions are asked, you're (hopefully
) set to go./Kenneth
-
But I really don't know what to ask to check if remote server is accessible.
Network guys use to change firewall settings without removing linked servers.
So, all my checks give me a green light, but when I run the query - it fails without any chance to catch an error.
And that's a problem!
_____________
Code for TallyGenerator -
Yes, that's a major PITA. I don't know of any 'inline' way to do that. The problem is that the only way to be certain the remote server is accessible, is to - access it.
And should it not be, then - bam.. code dies
There are ideas you could use if it's a question of jobs or batches, and that's to set up a separate jobstep as a 'connecting attempt', and then have the on error / success 'go to step n', or maybe implement the idea with scripts or batchfiles or whatever.. The important thing is that the remote login attempt must be done in a separate process, and the results somehow be relayed to the 'main' process or flow.
It's not very straight forward to do, that's for sure.
/Kenneth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply