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

The information in this article applies to:

Microsoft SQL Server 7.0
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:

One or more properties could not be set on the query for OLE DB provider 'SQLOLEDB'.

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:

The default value for the remote query timeout is 0.

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
					
```

Modification Type:	Major	Last Reviewed:	12/5/2003
Keywords:	kbprb KB256174