PRB: One or More Properties Cannot be Set on Query for OLE DB Provider SQLOLEDB

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: This error occurs:

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:

Steps to Reproduce Behavior

1. From any SQL Server Client like ISQL or Query Analyzer run this command:

   sp_configure 'remote query timeout', 65535
   reconfigure with override
   go
   					

2. Create a linked server by using the SQLOLEDB provider. Make sure that the remote procedure call (RPC) check box is selected.
3. Execute this query, which causes the error message to occur:

   exec <remoteserver>.master.dbo.sp_who