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

Question

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?

try changing 'and then' to '"and then"' – Bee_Riii Feb 08 '23 at 12:46 — Bee_Riii, Feb 08 '23 at 12:46

mclayton · Accepted Answer · 2023-02-08T15:49:23.193

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
"

Really great response. The link to MS' docs is especially useful for all the other ways of searching that would be useful to me. — Dude named Ben, Feb 08 '23 at 15:37
@DudenamedBen - npyw. Also, I just realised the "CONTAINS predicate" link was broken - it's fixed now... — mclayton, Feb 08 '23 at 15:50

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

1 Answers1