Article ID: 256174
Article Last Modified on 12/5/2003
APPLIES TO
- 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
SYMPTOMS
When you try to execute a distributed query by using the Microsoft SQL Server OLE DB provider, the following error might occur:
CAUSE
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:
Name | Minimum | Maximum | Config_value | Run_value |
---|---|---|---|---|
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.
RESOLUTION
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.
STATUS
This behavior is by design.
MORE INFORMATION
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:
exec <remoteserver>.master.dbo.sp_who
Additional query words: remote query timeout
Keywords: kbprb KB256174