How do I perform a MOSS FullTextSqlQuery and filter people results by the Skills managed property?

Question

I am having trouble with a MOSS FulltextSqlQuery when attempting to filter People results on the Skills Managed Property using the CONTAINS predicate. Let me demonstrate:

A query with no filters returns the expected result:

SELECT AccountName, Skills
from scope()
where freetext(defaultproperties,'+Bob')
And ("scope" = 'People')

Result

Total Rows: 1
ACCOUNTNAME: MYDOMAIN\Bob
SKILLS: Numchucks | ASP.Net | Application Architecture

But when I append a CONTAINS predicate, I no longer get the expected result:

SELECT AccountName, Skills
from scope()
where freetext(defaultproperties,'+Bob')
And ("scope" = 'People')
And (CONTAINS(Skills, 'Numchucks'))

Result

Total Rows: 0

I do realize I can accomplish this using the SOME ARRAY predicate, but I would like to know why this is not working with the CONTAINS predicate for the Skills property. I have been successful using the CONTAINS predicate with a custom crawled property that is indicated as 'Multi-valued'. The Skills property (though it seems to be multi-valued) is not indicated as such on the Crawled Properties page in the SSP admin site:

http:///ssp/admin/_layouts/schema.aspx?ConsoleView=crawledPropertiesView&category=People

Anyone have any ideas?

Answer 1

So with the help of Mark Cameron (Microsoft SharePoint Developer Support), I figured out that certain managed properties have to be enabled for full text search using the ManagedProperty object model API by setting the FullTextQueriable property to true. Below is the method that solved this issue for me. It could be included in a Console app or as a Farm or Web Application scoped Feature Receiver.

    using Microsoft.Office.Server;
    using Microsoft.Office.Server.Search.Administration;

    private void EnsureFullTextQueriableManagedProperties(ServerContext serverContext)
    {
        var schema = new Schema(SearchContext.GetContext(serverContext));
        var managedProperties = new[] { "SKILLS", "INTERESTS" };
        foreach (ManagedProperty managedProperty in schema.AllManagedProperties)
        {
            if (!managedProperties.Contains(managedProperty.Name.ToUpper()))
                continue;

            if (managedProperty.FullTextQueriable)
                continue;

            try
            {
                managedProperty.FullTextQueriable = true;
                managedProperty.Update();
                Log.Info(m => m("Successfully set managed property {0} to be FullTextQueriable", managedProperty.Name));
            }
            catch (Exception e)
            {
                Log.Error(m => m("Error updating managed property {0}", managedProperty.Name), e);
            }
        }
    }

Answer 2

SELECT AccountName, Skills
from scope()
where freetext(defaultproperties,'+Bob')
And ("scope" = 'People')
And (CONTAINS(Skills, 'Numchucks*'))

use the * in the end.

You also have a few more options to try:

The following list identifies additional query elements that are supported only with SQL search syntax using the FullTextSqlQuery class:

FREETEXT()

CONTAINS()

LIKE

Source