Enterprise Search query giving FaultException when using an ORDER BY

By | 2010-11-12

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:
0458_sp2010-search-checkbox_0C75FD0C

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()

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.