Exception when SQL query has multiple word string to W10 desktop search index

I’ve gobbled together a basic Powershell script to query W10’s Windows Desktop Search (WDS) index. Here is the relevant bits,

$query = "
    SELECT System.DateModified, System.ItemPathDisplay 
    FROM SystemIndex 
    WHERE CONTAINS(System.Search.Contents, '$($text)')
"

$objConnection = New-Object -ComObject adodb.connection
$objrecordset = New-Object -ComObject adodb.recordset

$objrecordset.CursorLocation = 3

$objconnection.open("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';")

$objrecordset.open($query, $objConnection, $adOpenStatic)

Until now my tests have been using single words and everything works. But when I started using two words, it falls apart with the following error,

Searching for 'and then'...

SELECT System.DateModified, System.ItemPathDisplay 
FROM SystemIndex 
WHERE CONTAINS(System.Search.Contents, 'and then')

Exception from HRESULT: 0x80040E14
At D:searchSystemIndex.ps1:72 char:1
+ $objrecordset.open($query, $objConnection, $adOpenStatic)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Using Explorer to query the index using content:"and then" works fine.

Any ideas?

Answer

According to the documentation for Windows Search SQL Syntax and the examples in the CONTAINS predicate, if you want to search for a literal phrase with “multiple words or included spaces” you need to quote the phrase inside the query:

Type: Phrase

Description: Multiple words or included spaces.

Examples

...WHERE CONTAINS('"computer software"')

So in your example you probably want:

$text = "and then"

$query = "
    SELECT System.DateModified, System.ItemPathDisplay 
    FROM SystemIndex 
    WHERE CONTAINS(System.Search.Contents, '`"$($text)`"')
"
#                                           ^^        ^^ 
#                                           quoted search phrase

(note the quotes are prefixed with a backtick as the quote would otherwise terminate your entire query string.)

If you’re not looking for the exact phrase “and then”, and you just want results that contain “and” and “then” it looks like you need to to do something like this:

Type: Boolean

Description: Words, phrases, and wildcard strings combined by using the Boolean operators AND, OR, or NOT. Enclose the Boolean terms in double quotation marks.

Example:

...WHERE CONTAINS('"computer monitor" AND "software program" AND "install component"')

...WHERE CONTAINS(' "computer" AND "software" AND "install" ' )

$query = "
    SELECT System.DateModified, System.ItemPathDisplay 
    FROM SystemIndex 
    WHERE CONTAINS(System.Search.Contents, '`"and`" AND `"then`"')
#                                          ^^^^^^^^^^^^^^^^^^^^^^
#                                          multiple independent words
"