MOSS FullTextSqlQuery API: Little Known Flags on Managed Properties

I just wrapped up a custom implementation of a faceted search web part that uses the MOSS Full Text Sql Query API. During the development, I ran into issues querying certain multi-valued Managed Properties, specifically Skills and Interests. In my query, I was using the CONTAINS predicate like such:

SELECT
UserProfile_GUID, PreferredName, JobTitle, Department, WorkPhone, OfficeNumber,
AboutMe, PictureURL, WorkEmail, WebSite, Path, HitHighlightedSummary,
HitHighlightedProperties, Responsibility, Skills, SipAddress
FROM scope()
WHERE freetext(defaultproperties,'+trent')
AND ("scope" = 'People')
AND CONTAINS(Skills, '"numchucks"')
AND CONTAINS(Skills, '"rockstar"')

Use of the CONTAINS predicate was purely due to my need to query multi-valued properties. If you are not querying a multi-valued property, you may simply use the ‘=’ or ‘LIKE’ predicates.

It turns out that the CONTAINS predicate will only work against managed properties that have been enabled as FullTextQueriable. I am not aware of a way to enable a property in the SSP Search Settings, so this has to be done using the API. I ended up including the following method as part of a Web Application scoped Feature Receiver to ensure certain Managed Properties were ‘FullTextQueriable’.

    using Microsoft.Office.Server;
    using Microsoft.Office.Server.Search.Administration;
 
    private void EnsureFullTextQueriableManagedProperties(ServerContext serverContext, params string[] managedPropertyNames)
    {
        var schema = new Schema(SearchContext.GetContext(serverContext));
        foreach (ManagedProperty managedProperty in schema.AllManagedProperties)
        {
            if (!managedPropertyNames.Contains(managedProperty.Name))
                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);
            }
        }
    }

So on the same note of Managed Property flags not visible in the SSP Search Settings, you may also want to know about the Retrievable flag. This flag prevents a Managed Property’s value from being returned if specified as a column in the SELECT statement.

The following table lists most of the out of the box Managed Properties and their default FullTextQueriable, HasMultipleValues, and Retrievable flag values.

Name FullTextQueriable HasMultipleValues Retrievable
AboutMe     X
Account X   X
AccountName     X
AssignedTo X   X
Assistant     X
Author X   X
Authority     X
BestBetKeywords X X X
Birthday     X
CachedPath     X
CategoryNavigationUrl X   X
CollapsingStatus     X
Company     X
contentclass X   X
ContentSource     X
ContentType X   X
Created     X
CreatedBy X   X
DataSource     X
DatePictureTaken     X
Department     X
Description X   X
DisplayTitle     X
DocComments X   X
DocKeywords X X X
DocSignature     X
DocSubject X   X
DottedLine     X
EMail X   X
EndDate     X
Fax     X
FileExtension     X
Filename X   X
FirstName X   X
FollowAllAnchor X    
HighConfidenceDisplayProperty1     X
HighConfidenceDisplayProperty10     X
HighConfidenceDisplayProperty11     X
HighConfidenceDisplayProperty12     X
HighConfidenceDisplayProperty13     X
HighConfidenceDisplayProperty14     X
HighConfidenceDisplayProperty15     X
HighConfidenceDisplayProperty2     X
HighConfidenceDisplayProperty3     X
HighConfidenceDisplayProperty4     X
HighConfidenceDisplayProperty5     X
HighConfidenceDisplayProperty6     X
HighConfidenceDisplayProperty7     X
HighConfidenceDisplayProperty8     X
HighConfidenceDisplayProperty9     X
HighConfidenceImageURL     X
HighConfidenceMatching   X X
HighConfidenceResultType     X
HireDate     X
HitHighlightedProperties     X
HitHighlightedSummary     X
HomePhone     X
Interests   X X
IsDocument     X
JobTitle     X
Keywords X   X
LastModifiedTime     X
LastName X   X
Location     X
Manager     X
MemberOf     X
Memberships X X X
MobilePhone     X
ModifiedBy X   X
MySiteWizard     X
NLCodePage      
Notes X   X
objectid     X
OfficeNumber     X
OWS_URL X    
PastProjects   X X
Path X   X
Peers   X X
PersonalSpace     X
PictureHeight X   X
PictureSize X   X
PictureThumbnailURL     X
PictureURL     X
PictureWidth X   X
PreferredName     X
Priority X   X
ProxyAddresses     X
PublicSiteRedirect     X
Purpose X   X
Rank     X
RankDetail     X
Responsibilities   X X
Schools   X X
SID     X
SipAddress     X
Site      
SiteName     X
SiteTitle     X
Size     X
Skills   X X
StartDate     X
Status X   X
Title X   X
UrlDepth X    
UserName     X
UserProfile_GUID     X
WebId     X
WebSite     X
WorkAddress X   X
WorkCity X   X
WorkCountry X   X
WorkEmail     X
WorkId     X
WorkPhone     X
WorkState X   X
WorkZip X   X

Tags: , , , , , ,

This entry was posted on Thursday, March 4th, 2010 at 12:56 pmand is filed under . You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

3 Responses to “MOSS FullTextSqlQuery API: Little Known Flags on Managed Properties”

Leave a Reply