Enterprise Search query giving FaultException when using an ORDER BY

  • Article

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, Path, Rank, Write FROM SCOPE() ";
query += "WHERE  ( (\"SCOPE\" = '";
query+= allSites;
query+= "') and ";
query += "((ItemContentType='Project Homepage') OR (ItemContentType='Blue 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:

sp2010-search-checkbox

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