SQLTeam.com | Weblogs | Forums

OLE DB provider "ASEOLEDB" for linked server returned message "[00000] [Native Code: 30149] [ASEOLEDB]The command has timed out."

Using a linked server from SQL Server 2008 to Sybase 15.4.
The testing of the Linked Server works correctly and once I selectr 'EXECUTE" I can see the Procedure running in Sybase but it times out at exactly 10 minutes.

Presumably you are expecting the Procedure to "take a long time" ?

And you are thinking that the 10-minutes, being a nice round number, is a timeout somewhere. Sounds like it to me too. Dunno how to fix that, but:

Any chance that you could Launch a task (at the Sybase end) to do the (long running) job, and then come back later and view the results - such that neither of those two things would take more than 10 minutes?

PITA but if you are in a hurry to get something working maybe that would do as a stop-gap.

Hopefully someone else knows where the 10-minute-timeout setting is. Short of the Client disconnecting, I think the server-to-server actions I have done with Linked Servers will run forever, until completion, unless I put a time limit on them.

I took the SQL that was meant to run through the linked server and running it in isql directly in Sybase. It has been running for over 20 mins. but not failing. Is there something in SQL Server that is forcing the SQL to timeout after 10 mins?

I checked the SQL DOCs :slight_smile: and, yeah, its there:

There is a Timeout setting on the Linked Server GUI form (I always set them up using T-SQL so not seen that, but I've checked and it is there). Default = 0 which is the configured timeout:

"Connection Timeout
Time-out value in seconds for connecting to a linked server.
If 0, use the sp_configure defaultremote query timeout Option value."

and there is indeed a "remote query timeout" in sp_config and ... guess what?!! ... mine is set to 600 seconds - Bingo! So that will probably be it.

"Set to 0 to disabled the timeout. Query will wait until cancelled.
The setting takes effect immediately without a server stop and restart.

P.S. if you are not expecting that query to take THAT long you might want to optimise it :slight_smile:

Thanks for the input Kristen!!
I left the Timeout setting on the Linked Server at 0 because all of the Linked Servers are set that way and there aren't any issues with them.
I next checked the "remote query Timeout" setting and ours is ALSO set at 600 seconds.
But before I set it to 0, I'm going to have the developer look at the code again. I can't believe they need this to run for over an hour like it has been doing!

Thanks again for the assistance!!!