December 11, 2012 at 8:40 am
I'm running the code below, and occasionally getting the following error:
Msg 7412, Sev 16, State 1, Line 10 : OLE DB provider "SQLNCLI10" for linked server "[ServerName]" returned message "Login timeout expired".
The error isn't unexpected...it's hitting a server over a WAN that's flaky sometimes, and occasional failures are expected and acceptable for what I'm doing. But the problem I have is it's blowing out of the TRY/CATCH and causing the whole query to die. Reading through the TRY/CATCH code on books online, it seems like an error below severity 20 should be caught and dealth with in the CATCH block. Anyone see anything obviously stupid I'm doing that's making it just explode?
Thanks!
BEGIN TRY
DELETE FROM [a local table]
INSERT INTO [a local table]
( ... )
SELECT
...
FROM
[RemoteServer].Database.dbo.Table
END TRY
BEGIN CATCH
PRINT ' Error Number: ' + CONVERT(VARCHAR(500), ERROR_NUMBER())
PRINT ' Error Severity: ' + CONVERT(VARCHAR(500), ERROR_SEVERITY())
PRINT ' Error State: ' + CONVERT(VARCHAR(500), ERROR_STATE())
PRINT ' Error Procedure: ' + CONVERT(VARCHAR(500), ERROR_PROCEDURE())
PRINT ' Error Line: ' + CONVERT(VARCHAR(500), ERROR_LINE())
PRINT ' Error Message: ' + CONVERT(VARCHAR(500), ERROR_MESSAGE())
END CATCH
--- Do a bunch of other stuff ---
GO
The Redneck DBA
December 11, 2012 at 8:47 am
Going out on a limb here, but I think it is because the connection is broken (or not established) even though it is not severity 20 or higher:
Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed.
December 11, 2012 at 9:06 am
I would tend to agree with Lynn. The connection is considered closed, so the error is lost.
Assuming that's true - you'd probably catch it if you wrap within a stored proc or an exec call.
----------------------------------------------------------------------------------
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?
December 11, 2012 at 9:09 am
Lynn Pettis (12/11/2012)
Going out on a limb here, but I think it is because the connection is broken (or not established) even though it is not severity 20 or higher:Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed.
By the way - you have a transcription error I think. This is straight from BOL (bold is mine):
A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
----------------------------------------------------------------------------------
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?
December 11, 2012 at 9:17 am
Hmm. Didn't catch that bit about "and the connection isn't closed" in BOL. Thanks.
I'll give the "wrap it in an exec" route a whirl and see what happens.
Thanks!
The Redneck DBA
December 11, 2012 at 11:42 am
Putting it in an EXEC('') seems to have done the trick. Thanks!
The Redneck DBA
December 11, 2012 at 12:28 pm
There are a number of things Try...Catch won't Catch, in T-SQL. Compile-time errors are one of those. That includes lost remote connections, since those have to be compiled into the execution plan, and thus require a call to the remote connection to make sure it's able to run what it'll be asked to run.
- 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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply