Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 256174 - Last Review: December 5, 2003 - Revision: 4.1
PRB: One or More Properties Cannot be Set on Query for OLE DB Provider SQLOLEDB
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 or more properties could not be set on the query for OLE DB provider 'SQLOLEDB'.
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.
Collapse this tableExpand this table
| 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.
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:
exec <remoteserver>.master.dbo.sp_who
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
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate