Microsoft KB Archive/295320

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

Article ID: 295320

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft Exchange 2000 Server Standard Edition

-



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:

&quot;Submitted SQL statement was incomplete.&quot; (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:  Create a new Standard EXE project. Remove the default form, &quot;Form1&quot;. Add a new Standard module to the project. Add a new reference to Microsoft ActiveX Data Objects 2.5 (or later).  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 = &quot;file://./backofficestorage/&quot; 'TODO:change <fqdn.domain.tld> to your domain setting. sConnString = sConnString & &quot;<fqdn.domain.tld>&quot; & &quot;/&quot; 'TODO:change to the to the folder that you want 'to search. For example, for public folders you will replace it    'with&quot;Public Folders&quot;. sConnString = sConnString & &quot; &quot;

Set oConn = New ADODB.Connection With oConn .Provider = &quot;ExOLEDB.DataSource&quot; .ConnectionString = &quot;Data Source=&quot; & sConnString .Open End With 'Find all items that end with the letter 'L'. sSQL = &quot;SELECT &quot;&quot;DAV:displayname&quot;&quot;, &quot;&quot;DAV:href&quot;&quot; &quot; sSQL = sSQL & &quot;FROM SCOPE('deep traversal of &quot;&quot;.&quot;&quot;') &quot; sSQL = sSQL & &quot;WHERE &quot;&quot;DAV:displayname&quot;&quot; LIKE '%L'&quot; Set oRS = oConn.Execute(sSQL) 'Error occurs here. With oRS Do While Not .EOF Debug.Print .Fields(&quot;DAV:href&quot;) .Move 1 Loop End With 'Release objects. oRS.Close Set oRS = Nothing oConn.Close Set oConn = Nothing End Sub </li> Make the changes in the code where specified by &quot;TODO&quot;.</li> Run the code.</li></ol>

Keywords: kbbug kbmsg kbpending KB295320

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.