How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Posted Thursday, March 6, 2008 11:15 AM by CoreyRoth

A while back, I posted a how to on using the KeywordQuery class and it seemed to get pretty good response, so I figured I would post a follow up on how to use the FullTextSqlQuery class today.  It's actually pretty similar to using the KeywordQuery class but of course the syntax is different.  I am not going to give you a full run down of all the syntax, but I will provide enough to get you going. 

There are a number of reason to use the FullTextSqlQuery class.  Using a SQL Syntax query unlocks the full power of Enterprise Search that really isn't there out of the box.  With SQL Syntax, you can do wildcard searches, and make use of the powerful CONTAINS and FREETEXT predicates.  With the CONTAINS predicate, you can use the FORMSOF term to do inflectional (i.e.: go matches going, gone, went, etc.) or thesaurus searches.  Again, check the SDK as there is plenty of documentation about how to use all sorts of predicates.  Before, we look at all the code lets look at a couple of things on the query.

SELECT Title, Path, Color, Size, Quantity, Rank, Description, Size FROM SCOPE() WHERE "scope" = 'My Scope' AND CONTAINS(Description, 'ant*')

In a lot of ways this looks like a T-SQL query.  Enterprise Search queries however always query from SCOPE().  To specify the actual scope (or scopes) you want to search on you use the where clause.  I have always found the syntax weird here because the word scope has to be in quotes and what you are comparing it to always has to be in single quotes.  In this case I am using the CONTAINS predicate to do a wildcard search.  It should return anything that has a word starting with ant in the description column (i.e.: ant, ants, anthony, antler, etc.).

Here is the complete code example.

using (SPSite siteCollection = new SPSite(siteCollectionUrl))

{

    // create a new FullTextSqlQuery class - use property intializers to set query

    FullTextSqlQuery myQuery = new FullTextSqlQuery(siteCollection)

    {

        QueryText = "SELECT Title, Path, Color, Size, Quantity, Description, Rank, Size FROM SCOPE() WHERE \"scope\" = 'My Scope' AND CONTAINS(Color, 'ant*')",

        ResultTypes = ResultType.RelevantResults

    };

 

    // execute the query and load the results into a datatable

    ResultTableCollection queryResults = myQuery.Execute();

    ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];

    DataTable queryDataTable = new DataTable();

    queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);

}

It really works the same as the KeywordQuery class so if you want an explanation of the details of the subsequent lines, be sure and check out that post.

Comments

# SharePoint Wildcard Search: "Pro" Is Not a Stem of "Programming"

Thursday, May 22, 2008 6:44 AM by Mirrored Blogs

On the MSDN search forum, people often ask a question like this: "I have a document named 'Programming

# SharePoint Kaffeetasse #68

Thursday, May 22, 2008 7:59 AM by SharePoint, SharePoint and stuff

Downloads Windows SharePoint Services 3.0 SP1 Virtual Disk mit Visual Studio Office SharePoint Server

# SharePoint Kaffeetasse #68

Thursday, May 22, 2008 9:05 AM by Mirrored Blogs

Downloads Windows SharePoint Services 3.0 SP1 Virtual Disk mit Visual Studio Office SharePoint Server

# Wildcard searches

Thursday, October 15, 2009 7:45 AM by Confluence: SharePoint 2010 Development Wiki

MOSS2007 search functionality is missing a common requirement of a search engine in the wildcard searches. This is commonly raised when the People Search is implemented and End Users wish to search for a person by a few characters of their Title etc.

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Wednesday, January 13, 2010 8:09 PM by guowei chen

how to write my sql text when i want that the results contain a compelete sentences.

eg: the result must have the sentence 'i am from china',how can i do?    the product we used is Search Server 2008. thank you !

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Tuesday, March 30, 2010 7:55 AM by Paul

Don't forget to call dispose() on your query object or wrap the FullTextSQLQuery in a using statement as it holds open resources.

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Sunday, November 7, 2010 1:36 AM by Mor

I get weird behavior by the Contains prediacte. using Contains(ColumnName,'test') would return "btesting" (part of word results) etc. why would I need then the wild card * in this case? and how do I get results like "this is a test", removing part of words results?

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Monday, January 10, 2011 7:20 AM by Andrew

I'm trying to use the FullTextSqlQuery to execute a search from inside a timer job... but it is not returning anything... the equivalent webpart does return results... Strange… and I’m not sure how to impersonate for the search…

# Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries

Pingback from  Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries

# SharePoint 2010 Enterprise Search KeywordQuery Class, KeywordQuery Class

Pingback from  SharePoint 2010 Enterprise Search KeywordQuery Class, KeywordQuery Class

# Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries

Pingback from  Use Keyword Query Syntax instead of SQL Syntax for SharePoint 2010 Search Queries

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Tuesday, May 17, 2011 2:49 AM by Nehal

I created a fulltextsqlquery in timerjob..it doesnot return anything..but when i did the same in console application i get the data..why this is so?

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Tuesday, May 17, 2011 8:50 AM by CoreyRoth

@Nehal When you run as a timer job you are running under a different account (the farm account).  This account should return results although you want to be careful because it may return results that you do not want.

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Monday, June 27, 2011 10:49 AM by Rajesh

Hi Corey Roth,

I have the same issue. When I execute the fultextquery through my webpart it works fine and get results. But when I run the FullTextQuery through a webservice call or httphandler, no results are returned. I have verified the query and its the same in both cases. Also there are no exceptions in both cases.

Looks like a security trimming issue. Is there a way to override security trimming?

Thanks

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Friday, July 1, 2011 9:44 AM by CoreyRoth

Well you need to look at the account executing the actual API call.  If you created a new application pool and it is running as local system or something like that it is not going to work.  Instead you need to run it with a different account that has access to SharePoint.  Keep in mind that you may not have proper security trimming if you use an account with full access to SharePoint though.

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Monday, July 18, 2011 12:52 AM by Nehal

I have created a fulltextsqlquery and it is bringing data on the site.It is a sitecollection feature.I checked the permissions on all levels and also checked the bdc permissions.Seems to be ok.But still my query is returning Zero rows.Although when i do the same in console application i get the result.I am trying this from a long time but havent reached the solution.I want to know the permissions required to run the fulltextsqlquery.please help

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Wednesday, July 20, 2011 9:10 AM by CoreyRoth

@Nehal The FullTextSqlQuery object will use whatever permissions the current user has.  It does this to apply security trimming.  If the current user does not have access then you won't get results.  However, another thing to note is that if the default content access account your crawl with has administrator privileges, it can mess up security trimming.

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Monday, January 30, 2012 1:08 PM by rocky

Hi

I have implemented fulltextquery as shown above and searching on contains(Title) column.

when I am searching for a file named

"marketing team collaboration.pdf" with words "team" or "marketing" or "collaboration" I get the result of the mentioned pdf file.

but when I try to search with words like "ketin" or "boratio" or "ea", I dont get any results.

Contains should return for these values too, why it isn't

Please advice me, thanks in advance.

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Friday, February 17, 2012 5:14 AM by Saumil

Hello CoreyRoth,

Your Sharepoint search blogs are really helpful..

I have posted a question to msdn sharepoint forums..

social.msdn.microsoft.com/.../6bd76724-2a0a-4130-b67d-50c760b23dfe

Please give some feedback on this..

Thanks,

Saumil

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Monday, February 20, 2012 8:48 PM by CoreyRoth

@Saumil I posted some feedback to your forum post.  

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Thursday, March 22, 2012 9:04 AM by deepppatel26

Hi to all,

what to do if i want to pass results on result.aspx page ?

how can i pass url query string to pass result?

Thanks

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Monday, April 2, 2012 10:58 PM by CoreyRoth

@deepppatel26 I'm afraid you can't out of the box.  If you are using SharePoint 2007, you can use the Wildcard Search Web Part and then customize the query by making your own customization.   wildcardsearch.codeplex.com

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Tuesday, May 15, 2012 9:28 PM by Charlie

There is a lot more hideousness to FullTextSqlQuery than is described here.

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Thursday, October 25, 2012 2:24 AM by Yasir

I need help regarding FullTextSqlQuery , I am using MOSS 2007 ,

Search is implemented and using FullTextSqlQuery  the results are being displayed,

I made changes to contents but they are not reflecting when data is fetched via FullTextSqlQuery,

already tried resetting crawl content , re running full crawl, resetting services , even restarting server,

but content it displays is not being updated, however when i used default search of moss 2007 using osssearchresults.aspx

it shows updated contents, from where FullTextSqlQuery retireves the results, why its not being updated? any help?

# re: How to: Use the MOSS Enterprise Search FullTextSqlQuery Class

Wednesday, November 7, 2012 10:44 AM by CoreyRoth

@Yasir I would avoid writing anything that uses Full Text SQL Query at this point as it has been removed from future versions of the product.

# Resources: SharePoint Development,Programming And PowerShell | lionadi

Pingback from  Resources: SharePoint Development,Programming And PowerShell | lionadi

# NothingButSharePoint.com

Friday, December 5, 2014 9:27 PM by NothingButSharePoint.com

Pingback from  NothingButSharePoint.com

Leave a Comment

(required)
(required)
(optional)
(required)