No error raised when a remote procedure times out - by Erland Sommarskog

Status : 

 


17
1
Sign in
to vote
ID 337043 Comments
Status Active Workarounds
Type Bug Repros 6
Opened 4/5/2008 8:40:00 AM
Access Restriction Public

Description

If you call a stored procedure in a linked server, and the provider times out, for 
instance there is a blocking situation on the remote server, SQL Server only prints
a message, but error is raised, so the condition cannot be caught in BEGIN TRY.

The problem appears both in SQL 2005 and SQL 2008.

As indicated in the repro, there is a workaround by looking at the return code
from the procedure.
Sign in to post a comment.
Posted by VTCarter on 4/18/2016 at 8:39 AM
This behavior also occurs in SQL 2014!
Posted by Erland Sommarskog on 7/14/2011 at 1:18 AM
Again you have closed it as "Won't Fix" without a comment. I refuse to accept this. This is a serious bug and I can't imagine that you are seriously considering ignoring it.
Posted by Erland Sommarskog on 3/16/2011 at 3:31 PM
I'm reopening this, as this should be fixed. Period.
Posted by Microsoft on 4/11/2008 at 4:10 PM
Erland,

Thanks for the reply. You are actually comparing the behavior of distributed querying (your "Select query" example) with that of RPC (calling a stored proc remotely). They are very different pieces of code that also behave differently.

Please rest assured that we are aware of the shortcomings with the error handling outlined in your example and that we have an open workitem to fix this problem in the next release.

Regards,

Joachim Hammer
Posted by Erland Sommarskog on 4/9/2008 at 1:06 PM
The problem is not really that the timeout error only appears as a message. The problem is that there is no other error to go with it. Look at this:

DECLARE @err int = 4711
begin try
PRINT 'Err is ' + ltrim(str(@err))
EXEC @err = YAZORMAN.Northwind.dbo.testis
PRINT 'After exec, @err is ' + coalesce(ltrim(str(@err)), 'NULL')
end try
begin catch
Print 'Error caught: ' + error_message()
end catch
PRINT 'After catch handler'
go
begin try
PRINT 'Before SELECT'
SELECT * FROM YAZORMAN.Northwind.dbo.Orders WHERE OrderID = 11000
PRINT 'This does not print'
end try
begin catch
Print 'Error caught: ' + error_message()
end catch
PRINT 'After catch handler'


The output is:

Err is 4711
OLE DB provider "SQLNCLI10" for linked server "YAZORMAN" returned message "Query timeout expired".
After exec, @err is NULL
After catch handler
Before SELECT
OLE DB provider "SQLNCLI10" for linked server "YAZORMAN" returned message "Query timeout expired".

(0 row(s) affected)
Error caught: Cannot fetch the rowset from OLE DB provider "SQLNCLI10" for linked server "YAZORMAN". .
After catch handler


As you see, the SELECT generates a level 16 error which is caught. You don't see
the timeout here, but if would run it without the CATCH handler you would.

OK, I can see from the text of the error message that this particular message is
not to be expected when you run a procedure, because you don't know that a
rowset is to be expected. Then again, the query timeout has an error level
when it's returned from the provider, so you should be able to raise an error,
and add the timeout as an informative error message like you do now.
Posted by Microsoft on 4/9/2008 at 11:38 AM
Erland,

Sorry for not responding sooner. This just was bad timing on our part. Dev and test closed the report before I had a chance to communicate with you.

The fact that you can’t catch the error from the time-out is a well-known problem that we haven't addressed well and uniformly across the engine yet. The problem is that the time-out error is printed as a warning – and warnings aren’t catchable.
As I mentioned this is a typical complaint about our try-catch and it occurs in other places.

Given that we are at the end of the current release cycle, a fix will not be feasible for Katmai. However, we are investigating a larger effort to provide better error handling and support in these and other cases for the next release.

Regards,

Joachim Hammer

Program Manager
SQL Server


Posted by Erland Sommarskog on 4/8/2008 at 1:06 PM
You have closed the bug gy design without a comment. Please understand that this about the most efficient way to insult a customer. May the customer has got it all wrong, but at least he deserves an explanation.

So could you please care to explain the "design" for this behaviour? What I failed to mention in my bug report, is that if I run the SELECT statement directly against
the linked server:

SELECT * FROM REMOTESVR.Northwind.dbo.Orders WHERE OrderID = 11000

you do get an error raised. So why is no error raised when a remote procedure call times out? It is not considered an error?