MOSS FullTextSqlQuery API: Little Known Flags on Managed Properties

By | March 4, 2010

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

3 thoughts on “MOSS FullTextSqlQuery API: Little Known Flags on Managed Properties

  1. Ken Foley

    what are you talking about? Speak english.

  2. Hans Sissing

    Thanks for this helpfull post, I was looking for an overview for which cloumns are full-text capable, found it!

Leave a Reply

Your email address will not be published. Required fields are marked *