Enterprise Search query giving FaultException when using an ORDER BY

Ran into this on a project when using the “FullTextSqlQuery” object to query against the Enterprise Search Service of SharePoint 2010.
Had a query that included an Order By clause on my own Managed property other than the normal RANK.

string query = "SELECT Title, ItemContentType, Projectnaam, Projectnummer, Projectomschrijving, Projectstatus, Projectlocatie, DeelprojectVan, Thema,Opdrachtgever,Projectlogo, Path, Rank, Write FROM SCOPE() ";
query += "WHERE  ( (\"SCOPE\" = '";
query+= allSites;
query+= "') and ";
query += "((ItemContentType='Project Homepage') OR (ItemContentType='Bouw Homepage')) ";
query += ") ";
query += "ORDER BY Projectnaam";

I kept getting an exception: “System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]”

The problem was my Managed Property could not be used as an order by property.

Solution is easy to fix by PowerShell or the Central Admin:
Go to your Search Service application, click on through to the Managed Property you want to ORDER BY and check this box ON:

The text actually says you need to disable the checkbox for order by to work, but it kind of works the other way around.
In powershell:


$searchAppName = "NAME OF YOUR SEARCH SERVICE APPLICATION"
$fieldName = "NAME OF YOUR MANAGED PROPERTY"

$searchapp = Get-SPEnterpriseSearchServiceApplication "$searchAppName"
$prop = Get-SPEnterpriseSearchMetadataManagedProperty -SearchApplication $searchapp $fieldName
$prop.MaxCharactersInPropertyStoreIndex = 0x40
$prop.Update()
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: