SQL Query Syntax Limits for OLE DB Provider
The following restrictions apply to the query syntax for OLE DB Provider for Commerce Server:
All members in all queries must be specified by their fully qualified names.
JOIN operations are not supported.
Active Directory does not support queries using the less than (<) or greater than (>) operators. For more information, see RFC 2254, "The String Representation of LDAP Search Filters".
Queries cannot be ordered by multiple properties when these properties are stored in Active Directory.
Membership Directory does not support sorting.
Nonprintable characters are not supported.
The results set from a SELECT query is streamed back in the order in which the underlying data store returns the data.
All members in the WHERE or ORDER_BY clauses must belong to the same underlying data store. If both WHERE and ORDER_BY clauses are present, then all members in both clauses must map to the same underlying data store.
Only single-valued Number (Integer, Float), String, Currency or Date/Time/Datetime data types are supported in the WHERE clause.
LIKE clauses support wildcard searches through the '%' character only. All members in LIKE clauses must be of the String data type.
The semicolon (;) is used as the delimiter for values in a multi-valued property. Thus, values in a multi-valued property must not contain a semicolon.
INSERT and UPDATE commands allow multi-valued property values. The following examples illustrate the use of multi-valued properties:
INSERT INTO UserObject (Name, Interests) VALUES ('Joe', ('Basketball', 'Baseball')) UPDATE UserObject SET (Interests = ('Basketball', Baseball')) WHERE Name = 'Joe'
A single quote must be escaped with another single quote.
A double quote does not need to be escaped with another double quote unless the query is in Visual Basic Scripting Edition (VBScript) code.
Copyright © 2005 Microsoft Corporation.
All rights reserved.