SQL Query Syntax Limits for OLE DB Provider

  • Article

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.