Friday, April 24, 2015

Trouble with Search using MSSQLFT and Like

I am using SPServices to run a search on a scope I have defined. When the page first loads, I run a query that retrieves all the items from the search scope using

var queryExtra = '';
var q = "SELECT Title,Path,Description,Write,Rank,Size,SiteTitle FROM SCOPE() WHERE ";
q += queryExtra + " (\"SCOPE\"='LC Engagement Sites') ORDER BY Rank";
                                   
var queryText = makeQuery(q);

$().SPServices({  
  operation: "Query",
  queryXml: queryText,  
  completefunc: function(xData, Status) { 

  })
})  
        

The function makeQuery turns the sql into the required xml.  When run, this works.

I also have a textbox that allows the user to show only ones where the title contains some text.  I have tried using like and contains to restrict the search but both return no results.

var queryExtra = getAll || title == "" ? "" : " Title like '%" + title + "%' AND ";

var queryExtra = getAll || title == "" ? "" : " CONTAINS(title,'" + title + "') and ";

The query ends up looking like this

SELECT Title,Path,Description,Write,Rank,Size,SiteTitle FROM SCOPE() WHERE  CONTAINS(title,'taiwan') and  ("SCOPE"='LC Engagement Sites') ORDER BY Rank

or 

SELECT Title,Path,Description,Write,Rank,Size,SiteTitle FROM SCOPE() WHERE  title LIKE '%taiwan%' and  ("SCOPE"='LC Engagement Sites') ORDER BY Rank

and the query packet looks like this

<QueryPacket xmlns='urn:Microsoft.Search.Query' Revision='1000'>
  <Query>
    <Context>
      <QueryText language='en-US' type='MSSQLFT'><!
        [CDATA[SELECT Title, Path, Description, Write, Rank, Size, SiteTitle 
        FROM SCOPE() 
        WHERE  CONTAINS(title,'taiwan') and  
         ("SCOPE"='LC Engagement Sites') ORDER BY Rank
        ]]>
      </QueryText>
    </Context>
    <IncludeSpecialTermResults>
      true
    </IncludeSpecialTermResults>
    <Range>
      <Count>1000</Count>
    </Range>
  </Query>
</QueryPacket>

Is there something I'm doing wrong? I've seen many examples that show that this is possible but so far no luck.

No comments:

Post a Comment