none
Convert FullTextSqlQuery into QueryText for SharePoint 2013 search RRS feed

  • Question

  • Hello experts,

    I want a quick assistance on converting FullTextSqlQuery into regular querytext as its depreciated in SP 2013. Will you please help?

    myQuery.QueryText = "SELECT FileName, Path, HitHighlightedSummary FROM SCOPE() WHERE \"scope\" = 'custSite' AND (FREETEXT(defaultproperties, '" + sAny1 + "') "
     + radioSearchOrdinal1.SelectedValue + " FREETEXT(defaultproperties, '" + sAny2 + "')) "
     + radioSearchOrdinal2.SelectedValue + " (FREETEXT(defaultproperties, '" + sAll1 + "') "
     + radioSearchOrdinal3.SelectedValue + " FREETEXT(defaultproperties, '" + sAll2 + "'))";  

    Also, is there a tool where I can use to convert this sql query into querytext?

    Many thanks

    Shri

    Thursday, January 5, 2017 2:04 AM

Answers

  • Hi,

    Try to use path to limit specific source, here is my test code for your reference.

    static void Main(string[] args)
            {
                SPSite site = new SPSite("http://sp:12001");
                SearchServiceApplicationProxy proxy = (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy(SPServiceContext.GetContext(site));
    
                using (KeywordQuery keywordQuery = new KeywordQuery(proxy))
                   {
                    keywordQuery.ResultsProvider = SearchProvider.Default;
                    keywordQuery.StartRow = 0;
                    keywordQuery.RowLimit = 500;
                    keywordQuery.EnableStemming = true;
                    keywordQuery.TrimDuplicates = false;
                    keywordQuery.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
                    keywordQuery.Culture = CultureInfo.CurrentCulture;
                    keywordQuery.KeywordInclusion = KeywordInclusion.AllKeywords;
                    keywordQuery.QueryText = "path:http://sp:12001/ AND Title:lee*";
                    keywordQuery.SelectProperties.Add("path");
                    keywordQuery.SelectProperties.Add("title");
                    keywordQuery.SelectProperties.Add("contentclass");
                    SearchExecutor executor = new SearchExecutor();
                    ResultTableCollection resultTableCollection = executor.ExecuteQuery(keywordQuery);
                    var resultTables = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults);
                    var resultTable = resultTables.FirstOrDefault();
                    }
                    Console.WriteLine("Done");
                    Console.ReadKey();
                }

    Reference code from.

    http://technologybooth.blogspot.sg/2013/08/keyword-query-in-sharepoint-2013.html

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, January 9, 2017 1:26 AM

All replies

  • Experts, please helps.

    Thanks

    ShriG

    Thursday, January 5, 2017 7:05 PM
  • Hi,

    You could use KeywordQuery and SearchExecutor in SharePoint 2013 search api.

    You could check below links for more details.

    https://msdn.microsoft.com/en-us/library/office/dn423226.aspx

    http://www.dotnetmafia.com/blogs/dotnettipoftheday/archive/2012/09/10/how-to-query-search-with-the-sharepoint-2013-client-object-model.aspx

    Here is one similar thread for your reference.

    https://social.msdn.microsoft.com/Forums/office/en-US/bdf60c4b-c7fd-4834-b0d1-a57e4f28de64/sharepoint-2013-and-fulltextsqlquery?forum=sharepointdevelopment

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, January 6, 2017 2:05 AM
  • Thanks Lee. I know the approach but I could not figure out how the specific site level scope query filter can be applied. All the link you mentioned has a query as just parameter but no specific details on how to write a scope for the QueryText. Could you please let me know if know  tools or throw some light converting?

    Thanks

    Shri

    Sunday, January 8, 2017 4:23 PM
  • Hi,

    Try to use path to limit specific source, here is my test code for your reference.

    static void Main(string[] args)
            {
                SPSite site = new SPSite("http://sp:12001");
                SearchServiceApplicationProxy proxy = (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy(SPServiceContext.GetContext(site));
    
                using (KeywordQuery keywordQuery = new KeywordQuery(proxy))
                   {
                    keywordQuery.ResultsProvider = SearchProvider.Default;
                    keywordQuery.StartRow = 0;
                    keywordQuery.RowLimit = 500;
                    keywordQuery.EnableStemming = true;
                    keywordQuery.TrimDuplicates = false;
                    keywordQuery.AuthenticationType = QueryAuthenticationType.PluggableAuthenticatedQuery;
                    keywordQuery.Culture = CultureInfo.CurrentCulture;
                    keywordQuery.KeywordInclusion = KeywordInclusion.AllKeywords;
                    keywordQuery.QueryText = "path:http://sp:12001/ AND Title:lee*";
                    keywordQuery.SelectProperties.Add("path");
                    keywordQuery.SelectProperties.Add("title");
                    keywordQuery.SelectProperties.Add("contentclass");
                    SearchExecutor executor = new SearchExecutor();
                    ResultTableCollection resultTableCollection = executor.ExecuteQuery(keywordQuery);
                    var resultTables = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults);
                    var resultTable = resultTables.FirstOrDefault();
                    }
                    Console.WriteLine("Done");
                    Console.ReadKey();
                }

    Reference code from.

    http://technologybooth.blogspot.sg/2013/08/keyword-query-in-sharepoint-2013.html

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, January 9, 2017 1:26 AM
  • Thanks again, these are great resources but these scopes were created during the SharePoint 2013 Preview version. These are no longer supported as is if I send querytext, it throws error as unsupported scenario.

    I don't know if I have to create a result source and then pass 'Result scope' parameter but there are no syntax/blog or any other source. I have also created a result source but I don't know if I have to pass as "scope:result source" or any other ways. I thought by limiting the site scope, I can filter but its not possible. The following code return results from other subsites and root site as well eventhough I pass as subsite url.

               using (SPSite siteCollection = new SPSite("http://server/site/customSite"))

                {
                    KeywordQuery keywordQuery = new KeywordQuery(siteCollection);
                    keywordQuery.QueryText = "SharePoint" + " " + "Search";

                    SearchExecutor searchExecutor = new SearchExecutor();
                    ResultTableCollection resultTableCollection = searchExecutor.ExecuteQuery(keywordQuery);
                    var resultTables = resultTableCollection.Filter("TableType", KnownTableTypes.RelevantResults);
                    var resultTable = resultTables.FirstOrDefault();
                    DataTable dataTable = resultTable.Table;

                }

     

    I refered resources from Corey Roth but it doesn't talk about limiting/filtering site level returns.

    Any other advise or quick conversion help?

    Monday, January 9, 2017 3:53 AM
  • Hi,

    I updated previous post based on my test, hope this could help.

    Best Regards,

    Lee


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, January 9, 2017 9:59 AM
  • Thanks much Lee. You rock!

    The following code worked to trim down the scope.

    keywordQuery.QueryText = "path:http://sp:12001/ AND Title:lee*";

    Monday, January 9, 2017 9:38 PM