SharePoint 2010 Server Search: FullTextSqlQuery issue with LIKE

Question

I am working on SharePoint 2010 Server Search using FullTextSqlQuery. My QueryText also contains ORDER BY keyword. However, if property uses in ORDER BY query, it must have to check the option "Reduce storage requirements for text properties by using a hash for comparison" for it to work http://msdn.microsoft.com/en-us/library/ms497585.aspx

The query text below will cause exception if it returns data, otherwise if there is no data return, no exception will show.

SELECT Author,Title,FileExtension,Path 

FROM Scope()  WHERE ("Scope" = 'All Sites')  AND FileExtension = 'ASPX'

AND Title like 'A%'

Exeception :

System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Error HRESULT E_FAIL has been returned from a call to a COM component. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:

System.Runtime.InteropServices.COMException: Error HRESULT E_FAIL has been returned from a call to a COM component. 

at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)  

at Microsoft.Office.Server.Search.Query.FullTextSqlQueryInternal.Execute()  

at Microsoft.Office.Server.Search.Query.QueryInternal.Execute(QueryProperties properties)  

at Microsoft.Office.Server.Search.Administration.SearchServiceApplication.Execute(QueryProperties properties)   at SyncInvokeExecute(Object , Object[] , Object[] )  

at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)  

at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)  

at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)    at System.ServiceModel.Dispatcher.ImmutableDispatchRunt...).

However, if we change the query text into below, there is NO error.

SELECT Author,Title,FileExtension,Path 

FROM Scope()  WHERE ("Scope" = 'All Sites')  AND FileExtension = 'ASPX'

AND Title like 'A%'

ORDER BY Title

Title property is checked option “Reduce storage requirements for text properties by using a hash for comparison”

Is there any ways to fix this issue?

Workaround: Always adding ORDER BY to the query.

Code:

I use code below to execute the search:

    using Microsoft.Office.Server.Search.Query;

    private DataTable Execute(string strQuery)
    {      
      DataTable results = new DataTable();

      using (SPSite site = new SPSite("http://localhost:2345"))
      {
        FullTextSqlQuery fts = new FullTextSqlQuery(site);
        fts.TrimDuplicates = false;
        fts.QueryText = strQuery;
        fts.ResultTypes = ResultType.RelevantResults;
        fts.RowLimit = 300;

        ResultTableCollection rtc = fts.Execute();
        if (rtc.Count > 0)
        {
          using (ResultTable relevantResults = rtc[ResultType.RelevantResults])
            results.Load(relevantResults, LoadOption.OverwriteChanges);
        }
        
        return results;
      }
    }

Answer 1

This is a very annoying problem I encountered as well.

The default configuration of SharePoint Search does not allow you to sort on the Title field! This can be fixed by changing a new setting on the managed property introduced in SharePoint 2010.

Go to the managed property settings for "Title" and check the field called "Reduce storage requirements for text properties by using a hash for comparison.". Recrawl the site and it should be fixed.

This is documented by my colleage Michiel Lankamp on his blog: http://www.sharepointblog.nl/2010/05/25/fulltextsqlquery-exception-from-hresult-0x80040e60/

Answer 2

Here are resolution to workaround:

Resolution:

·          Always add the “order by Title” filter statement and enable the "Reduce storage requirements for text properties by using a hash for comparison" for the Title property.

·          Filed this behavior as a bug at connect: https://connect.microsoft.com/site428/feedback/details/587074/fulltextsqlquery-bug-error-hresult-e-fail-for-specific-search-statement .

Answer 3

according to http://technet.microsoft.com/en-us/library/cc164182.aspx , select the Reduce storage requirements for text properties by using a hash for comparison check box if you only need this property to have equality/inequality comparisons. Leave the check box cleared if you need to enable other types of comparisons (such as less than, greater than, and order by).

But for the Like in Where to work, it seems that the
Reduce storage requirements for text properties by using a hash for comparison has to be unchecked. Here conflict comes. A workaround is to use the full text predicate CONTAINS (http://msdn.microsoft.com/en-us/library/ms513993.aspx).