none
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;
          }
        }
    

    Friday, August 13, 2010 2:44 AM

All replies