July 2, 2003 at 12:06 am
IT providers run our government SQL Server 7 installation with the ‘remote query timeout’ changed from the default of unlimited (0) to 10 minutes (600). This has the unintended result of failing select statements to the linked servers set up to add/update 5 tables with up to 30,000 rows of data once a month. It doesn’t actually time out, a 7370 error presents immediately irrespective of whether the timeout is set to over a day or under a minute. Microsoft engineers have confirmed this situation and their recommended action is to return the setting to 0 or upgrade to SQL2000. We are unable to upgrade because of front end incompatibilities (16 bit ODBC issues) and the IT providers inform us that they will not change their policy on this issue. We even asked if this dynamic property could be changed for an hour or two every month but this apparently creates an unacceptable risk to other databases.
Our budget does not currently stretch to alternatives such as coding a VB object or setting up a DTS package and the suppliers are not liable for the failure as the product was proven on the developers system, our test system and the Governments own test system (because they use the default setting on that server).
So, is anyone aware of any risks associated with setting the remote query timeout to 0? Alternatively, are there any other cheap solutions? We are not permitted to change IT providers btw.
Thanks
’pling (Kevin Prideaux)
July 2, 2003 at 1:11 am
Kevin,
I personally haven't had any problems with Remote QUERY Timeout set to zero on SQL 7.0 or SQL 2000. But I have had problems with Remote LOGIN Timeout set to infinite. Eg. Trying to log into a dead box results in a hanging process.
Are the linked servers shared by other customers of the IT providers? If so, then I sort of understand their position, but they should justify it anyway. If it's just your server then I find it hard to figure out why thay'd take such a stance.
Cheers,
- Mark
July 2, 2003 at 1:58 am
Thanks for the response. The linked servers are actually named ranges in Excel files. They are only set up for me, and my login is the only one set with permissions to access them.
The server is shared across many departments. The explanation given to me for changing the default timeout from infinite to 10 minutes is to prevent a situation where significant resources are consumed or processes are tied up by many long running queries or ones which haven’t terminated. They haven’t cited any evidence that this has occurred in the past or might occur, they just seem to have a policy of having no infinite timeouts. It is interesting to note that SQL2000 has a default of 600, but of course it doesn’t cause these errors (also verified by Microsoft).
If you search www on ‘remote query timeout’ and refine it, there is scant information out there in the world, which suggests to me that there are very few instances where leaving it at zero has been problematic.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply