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.

April 24th, 2015 4:41pm

Hi,

The following code for your reference:

<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <meta name="WebPartPageExpansion" content="full" />
        <script language="javascript" src="jquery-1.8.2.min.js"></script>
        <script language="javascript" src="jquery.SPServices-0.7.2.min.js"></script>
        <script language="javascript">
            (function () {
                function querySearchService(scopeName, keywordToSearch, maxItemCount) {
                    keywordToSearch = keywordToSearch.trim().replace(/\s+/g, "+");
                    var queryText = "<QueryPacket xmlns='urn:Microsoft.Search.Query' Revision='1000'>"
                    queryText += "<Query>"
                    queryText += "<Range><StartAt>1</StartAt><Count>" + maxItemCount + "</Count></Range>"
                    queryText += "<Context>"
                    queryText += "<QueryText language='en-US' type='MSSQLFT'>"
                    queryText += "SELECT Title,Path,Description,Write,Rank,Size,SiteTitle FROM scope() WHERE CONTAINS ('" + keywordToSearch + "') AND \"scope\"='" + scopeName + "' AND ISDOCUMENT=true ORDER BY Rank DESC"
                    queryText += "</QueryText>"                      
                    queryText += "</Context>"
                    queryText += "</Query>"
                    queryText += "<EnableStemming>True</EnableStemming>"
                    queryText += "<TrimDuplicates>True</TrimDuplicates>"
                    queryText += "</QueryPacket>";
                    //alert(queryText);
                    $().SPServices({
                        operation: "Query",
                        queryXml: queryText,
                        completefunc: function (xData, Status) {
                            $(xData.responseXML).find("QueryResult").each(function () {
                                var array = new Array();
                                alert($(this).text());
                                var result = $.parseXML($(this).text());
                                var matches = result.find("Document");
                                if (matches.length == 0) {
                                    alert("Nothing Found!");
                                    return;
                                }
                                $("#dynamic-search-results").empty();
                                matches.each(function () {
                                    url = $("Properties>Property:nth-child(2)>Value", $(this)).text();
                                    title = $("Properties>Property:nth-child(1)>Value", $(this)).text();
                                    $("#resultDiv").append($("<p></p>").append($("<a></a>").prop("href", url).text(title)));
                                });
                            });
                        }
                    });
                }
                $(document).ready(function () {
                    querySearchService("LC Engagement Sites", "SharePoint", 10);
                });
            })();
        </script>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Untitled 2</title>
    </head>
    <body>
        <div id="resultDiv"></div>
    </body>
</html>

More information is here:

http://jeffywang.blogspot.com/2014/01/utilizing-spservices-to-call-sharepoint.html

Best Regards

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 3:28am

Thanks, but this doesn't allow me to search a specific field.  I need to return only those items where the title matches the search keyword. Is this possible?
April 27th, 2015 10:41am

Argh, found the problem.  I left out the wildcards on the contains function

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

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 11:33am

Hi,

Thanks for sharing! It will help others who suck with the problem!

Best Regards

April 27th, 2015 9:25pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics