Accessing custom crawled property in fulltextsqlquery

Question

My manage property is named like e/m/BioOffice/Text, e/m/BioPosition/Text. When I try to execute the following code:

 string queryTxt = "SELECT URL, e/m/BioOffice/Text,e/m/BioPosition/Text FROM SCOPE() where \"scope\"='ektron25056203187' And FREETEXT(*,'" + TextBox1.Text.Trim() + "')";


                var query = new FullTextSqlQuery(searchApplicationProxy)
                {   
                    QueryText = queryTxt,
                    ResultTypes = ResultType.RelevantResults,
                    ResultsProvider = SearchProvider.Default,
                    TrimDuplicates = false,
                    EnableStemming = true
                };

                ResultTableCollection resultsTableCollection = query.Execute();
                ResultTable searchResultsTable = resultsTableCollection[ResultType.RelevantResults];                   
                DataTable resultsDataTable = new DataTable();
                resultsDataTable.TableName = "Results";
                resultsDataTable.Load(searchResultsTable, LoadOption.OverwriteChanges);

                Label1.Text = "Total results Count : " + resultsDataTable.Rows.Count;

It give me the exception : Your query is malformed. Please rephrase your query. Please help how I can access those properties.

Answer 1

Hi gaurav test your query in Search Service Tool

The SharePoint Search Service Tool is a rich web service client that allows a developer to explore the scopes and managed properties of a given SharePoint Search SSP, build queries in either Keyword or SQL Syntax, submit those queries and examine the raw web service results. This tool can be useful in troubleshooting and verifying the behavior and configuration of a SharePoint environment.

you can fiend that tool in codeplex

http://sharepointsearchserv.codeplex.com/

Answer 2

Search Server interprets this as a malformed query due to the forward slashes ('/') in your managed property names. These property names should be surrounded with double quotes (") so that Search Server's query parser interprets them literally. (Note how the 'scope' property is referenced in the WHERE clause later in the expression.)

string queryTxt = "SELECT URL, \"e/m/BioOffice/Text\",\"e/m/BioPosition/Text\" FROM SCOPE() where \"scope\"='ektron25056203187' And FREETEXT(*,'" + TextBox1.Text.Trim() + "')";

I also see that you are likely integrating an Ektron site with Search Server. I would recommend using Ektron search API to ensure that your queries are well-formed. (Reference: AdvancedSearchCriteria)