BUG: Unable to Specify Leading Wildcard (%) with LIKE Operator in Exchange 2000 Query (295320)


The information in this article applies to:

  • Microsoft Exchange 2000 Server
This article was previously published under Q295320

SYMPTOMS

When you submit a SQL query that specifies a leading wildcard (%) in the LIKE operator for the WHERE clause, you may receive the following error:
"Submitted SQL statement was incomplete." (0x80550023)

CAUSE

The LIKE operator for Exchange 2000 queries only supports either a trailing wildcard or both leading and trailing wildcards; for example: 
    LIKE 'M%'
    LIKE '%M%'

WORKAROUND

If you need to search by values that end in a particular substring, specify both leading and trailing wildcards (for instance, LIKE '%substring%'), and then process the results.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

To reproduce the problem in Microsoft Visual Basic, follow these steps:
  1. Create a new Standard EXE project.
  2. Remove the default form, "Form1".
  3. Add a new Standard module to the project.
  4. Add a new reference to Microsoft ActiveX Data Objects 2.5 (or later).
  5. Paste the following code into the module:
    Public Sub MAIN()
    Dim oConn As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim sConnString As String
    Dim sSQL As String
    
    sConnString = "file://./backofficestorage/"
    'TODO:change <fqdn.domain.tld> to your domain setting.
    sConnString = sConnString & "<fqdn.domain.tld>" & "/"
    'TODO:change <folder> to the to the folder that you want 
    'to search. For example, for public folders you will replace it 
    'with"Public Folders".
    sConnString = sConnString & "<folder>"

    Set oConn = New ADODB.Connection
    With oConn
        .Provider = "ExOLEDB.DataSource"
        .ConnectionString = "Data Source=" & sConnString
        .Open
    End With
    
    'Find all items that end with the letter 'L'.
    sSQL = "SELECT ""DAV:displayname"", ""DAV:href"" "
    sSQL = sSQL & "FROM SCOPE('deep traversal of "".""') "
    sSQL = sSQL & "WHERE ""DAV:displayname"" LIKE '%L'"
    
    Set oRS = oConn.Execute(sSQL)  'Error occurs here.
    With oRS
        Do While Not .EOF
            Debug.Print .Fields("DAV:href")
            .Move 1
        Loop
    End With
    
    'Release objects.
    oRS.Close
    Set oRS = Nothing
    oConn.Close
    Set oConn = Nothing
End Sub
  6. Make the changes in the code where specified by "TODO".
  7. Run the code.
Modification Type:MinorLast Reviewed:3/4/2004
Keywords:kbbug kbMsg kbpending KB295320
©2004 Microsoft Corporation. All rights reserved.