Excluding empty text values in SharePoint Search

Last week I got an opportunity to work with SharePoint Search. As you could imagine it didn’t go that easy at all and to be honest the SQL-like statements didn’t make it for me at all. And unless I’ve seriously missed something, I’ve found out that there is no easy way to exclude empty text results in SharePoint Search. At least it seemed so…

In my case I’ve had a MultiLookup field (Tags) attached to some Content Types. What I wanted to achieve is to obtain a list of all tags and the number of times they were used. SQL Syntax for Windows SharePoint Services Search doesn’t provide any grouping mechanism as far as I can tell. And even if it would, I don’t think it would allow you to group all results by particular values of a MultiLookup and perform a count.

To get the result described above I would have to use a rather slow while loop to iterate through all the results. Minimizing the number of results would improve this part of the process.

In my scenario not all items were tagged. Excluding items without tags from the search results would decrease the collection of items which would have to be iterated.

For the purpose of the case I have created a Text Managed Property based on Tags field. I assumed that the value of the Tags field, for all items without tags would be NULL. According to MSDN you can exclude NULL values using the IS NOT NULL predicate. Wrong!

Running the query:

SELECT Tags FROM portal..scope() WHERE "SCOPE" = 'My scope' AND Tags IS NOT NULL

would result in the following exception:

Exception from HRESULT: 0×80044100

Query machine 'VPC-MOSSDEV' has been taken out of rotation due to this error: Values of this type are not turned into strings necessary to generate scope information. The item will not be locatable in all of the appropriate scopes but this is not a serious error.   0×80044100.  It will be retried in 15 seconds.

I’m not really sure what the reason of such behavior is. Is it by-design SharePoint behavior, does it have anything to do with the MultiLookup field being converted to a Text Managed Property or is it even something else?

What I’ve found out by trial and error is that changing the IS NOT NULL predicate to > ‘’ works!

SELECT Tags FROM portal..scope() WHERE "SCOPE" = 'My scope' AND Tags > ''

After having modified the query as displayed above, the query returned results as expected. So if you ever find yourself having troubles with NULL values, the above might perhaps help you as well.

Bookmark this post

Possibly related posts

• Inconvenient Reusable Content List
• Inconvenient Pages List Name
• Inconvenient body id for anonymous users
• Inconvenient SPSecurityTrimmedControl revisited
• Inconvenient PublishingScheduleControl