March 13, 2008 at 9:48 am
We are having an issue with a TRY/CATCH construct not working as expected.
We are running the following statement, executed on a SQL Server 2005 instance (the linked server, LINKSERV, is a SQL 2000 instance):
BEGIN TRY
EXEC LINKSERV.DBNAME.dbo.sprocName
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
Inside the sproc, sprocName, we have the following code (note, this is sql 2000 code):
SET @ErrMsg = 'error message'
GOTO ErrorHandler
ErrorHandler:
SELECT @ErrMsg = 'ERROR: %d : ' + @ErrMsg
RAISERROR (@ErrMsg, 11, 2, @@ERROR) WITH SETERROR
RETURN -1
The issue is that the error is not trapped in the TRY portion of the calling code - first excerpt above - so the CATCH portion is never reached.
Instead the actual sproc call bombs with the error.
If someone has some ideas why this is happening, it would be much appreciated.
Thank you
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 14, 2008 at 7:31 am
Come on guys, no takers on this? :w00t:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 14, 2008 at 8:10 am
I can't help but think that somehow it's happening in a different context, or that somehow it's raising an error on the "wrong server". There are a few of those where the error gets passed to the "higher level" to get caught, and I'm not 100% why it wouldn't get in the current context.
Can you replicate that with a local SP? Do you "see" the -1 return code or not?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 12:17 pm
Matt Miller (3/14/2008)
I can't help but think that somehow it's happening in a different context, or that somehow it's raising an error on the "wrong server". There are a few of those where the error gets passed to the "higher level" to get caught, and I'm not 100% why it wouldn't get in the current context.Can you replicate that with a local SP? Do you "see" the -1 return code or not?
Yes, I do see the -1 return code, when running the code with a local SP. I created an SP - test_TRY_CATCH - on the 2005 instance and put the following code inside:
DECLARE @ErrMsg AS VARCHAR(30)
SET @ErrMsg = 'error message'
GOTO ErrorHandler
ErrorHandler:
SELECT @ErrMsg = 'ERROR: %d : ' + @ErrMsg
RAISERROR (@ErrMsg, 11, 2, @@ERROR) WITH SETERROR
RETURN -1
Then I executed the sproc like this:
DECLARE@return_value int
EXEC@return_value = [dbo].[test_TRY_CATCH]
SELECT'Return Value' = @return_value
I got the following output:
Msg 50000, Level 11, State 2, Procedure test_TRY_CATCH, Line 17
ERROR: 0 : error message
Return Value
------------
-1
Any ideas? :ermm:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 14, 2008 at 12:20 pm
I did the exact same test on the sql 2000 instance - the linked-server instance - and got the same output:
Msg 50000, Level 11, State 2, Procedure test_TRY_CATCH, Line 17
ERROR: 0 : error message
Return Value
------------
-1
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 14, 2008 at 12:24 pm
I would tend to think that the problem is that it's running the called proc on SQL 2000, which doesn't have Try/Catch. But that's just a guess based on what I'm seeing here. I'll have to test some stuff on this to prove/disprove this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 14, 2008 at 12:35 pm
GSquared (3/14/2008)
I would tend to think that the problem is that it's running the called proc on SQL 2000, which doesn't have Try/Catch. But that's just a guess based on what I'm seeing here. I'll have to test some stuff on this to prove/disprove this.
That was my first response too, but, according to the developer, the same code is working fine on a second environment. I've asked them for more info so I can compare the 2 environments to see if I can come up with some clues.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 14, 2008 at 12:37 pm
I just tried this on two linked servers. The calling server is SQL 2005 (of course), one link is to SQL 2000, the other to SQL 2005.
create proc ErrorTest
as
declare @ErrMsg varchar(50)
SET @ErrMsg = 'error message'
GOTO ErrorHandler
ErrorHandler:
SELECT @ErrMsg = 'ERROR: %d : ' + @ErrMsg
RAISERROR (@ErrMsg, 16, 2, @@ERROR) WITH SETERROR
RETURN -1
begin try
exec @err = linkedserver.testdb.dbo.errortest
end try
begin catch
print 'Catch'
end catch
The catch portion of the script never fired for either server.
When I modified the calling procedure:
declare @Err int
begin try
exec @err = linkedserver.testdb.dbo.errortest
if @err <> 0
raiserror('Error trap', 16, 1)
end try
begin catch
print 'Catch'
end catch
I got this result:
Msg 50000, Level 16, State 2, Procedure ErrorTest, Line 10
ERROR: 0 : error message
Catch
Which disproves my original idea, but does give an idea on how to handle this.
Possibly Try/Catch doesn't work as expected for linked servers. That's important to know.
I also tried another test, where I used Try around a select from a table that doesn't exist, on one of the linked servers, and the Catch function worked as expected on that. So it might be a problem with specific errors in the remote proc? Not sure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 14, 2008 at 12:42 pm
Thank you GSquared, this was very informative.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 14, 2008 at 2:31 pm
When wrapping the whole thing in a transaction, it works and the CATCH block is reached!
BEGIN TRAN
BEGIN TRY
EXEC LINKSERV.DBNAME.dbo.sprocName
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
ROLLBACK TRAN
So it seems that a linked server call (to a sql 2000 instance) will not behave properly in a TRY/CATCH block unless the whole thing is wrapped in a transaction (?!).
Does that make sense?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 14, 2008 at 2:32 pm
It makes sense in that the transaction fails, so the catch is called.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 14, 2008 at 3:11 pm
Actually, I'm not sure this explains it. The TRANSACTION is wrapped around the TRY/CATCH block, not the other way around.
I would have expected the failure of the transaction to be "caught" in the CATCH block if the TRAN was defined INSIDE the TRY block.
Make sense?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 17, 2008 at 9:38 am
Shouldn't matter regarding the sequence. The whole idea of Try/Catch is that if any error with a severity greater than 10 (I think that's the minimum) comes up, it pops over to the Catch block.
The fact that a direct call to a proc on another server that specifically returns a -1, doesn't fire off the Try/Catch, is the part that doesn't make sense to me. All of the rest is a local error directly from the server and it makes sense that it would catch it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply