Microsoft KB Archive/256174
Article ID: 256174
Article Last Modified on 12/5/2003
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft Data Access Components 2.0
- Microsoft Data Access Components 2.1
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft OLE DB Provider for SQL Server 7.0
- Microsoft OLE DB Provider for SQL Server 7.01
This article was previously published under Q256174
When you try to execute a distributed query by using the Microsoft SQL Server OLE DB provider, the following error might occur:
One reason that this message may occur is that the value for a remote query timeout in the local server is set in an invalid range for the underlying provider. SQL Server allows you to use values that range between 0 - 2147483647 for a remote query timeout.
When you run this code:
sp_configure 'remote query timeout'
This error occurs:
|remote query timeout (s)||0||2147483647||0||0|
The SQL Server OLE DB provider (SQLOLEDB) has the maximum query timeout value defined as 0xfffe, which is 65534. When the remote query timeout is set to anything higher than 65534, SQL Server tries to set the DBPROP_COMMANDTIMEOUT to the specified value that happens to be greater than the allowed limit of 65534 in the SQLOLEDB provider, which then causes the error message to occur.
Use a value less than or equal to 65534 when you use the SQL Server OLE DB provider (SQLOLEDB). Please note that this is a server level option and some OLE DB providers might not support this property in which case the timeout value should be set to 0.
This behavior is by design.
The extended error information from the OLE DB error trace event shows this information:
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandProperties::SetProperties returned 0x80040e21: [PROPID=DBPROP_COMMANDTIMEOUT VALUE=65536 STATUS=DBPROPSTATUS_BADVALUE]].
Steps to Reproduce Behavior
From any SQL Server Client like ISQL or Query Analyzer run this command:
sp_configure 'remote query timeout', 65535 reconfigure with override go
- Create a linked server by using the SQLOLEDB provider. Make sure that the remote procedure call (RPC) check box is selected.
Execute this query, which causes the error message to occur:
Additional query words: remote query timeout
Keywords: kbprb KB256174