Custom Search Solutions for SharePoint 2010 - FullTextSqlQuery

If you are building a custom search solution based on SharePoint 2010 you might want to build your own custom query to query the SharePoint index. If you do you probably need to use the FullTextSqlQuery class, just like I am for my custom Birthday web part. While building that web part I ran into a couple of things that I might be able to spare others.

First thing to note is that the FullTextSqlQuery is available in two different namespaces. There is Microsoft.SharePoint.Search.Query.FullTextSqlQuery for SharePoint Foundation and there is Microsoft.Office.Server.Search.Query.FullTextSqlQuery for SharePoint Server. Be aware of which version of SharePoint you are running and use the right one, or your query won’t return the results you expect. So if you are running SharePoint Server you need to use Microsoft.Office.Server.Search.Query.FullTextSqlQuery.

Apart from this there don’t seem to be too many changes in the FullTextSqlQuery class. The SharePoint Search SQL Syntax for SharePoint2010 isn’t documented yet, but from what I saw I think Microsoft can just copy the documentation from the SharePoint 2007 version. I was particularly interested in the DATEADD clause. The DATEADD clause in SharePoint was developed to retrieve last modified documents within an environment. You could for example use WHERE LastModifiedTime <= DATEADD (DAY, -5, GETGMTDATE()), which would get you the documents modified within the last 5 days.

My use for the DATEADD clause in my birthday web part would be to retrieve the birthdays in the next few days. So I would want to use the DATEADD clause with a positive value, for instance 7 for the birthdays in the next week. Unfortunately the SharePoint Search SQL Syntax still doesn’t support using positive values in the DATEADD clause.
Luckily for me at least I can use LINQ now to run this query on the resultset I get back after running my FullTextSqlQuery. :-)

Comments -
  1. Gravatar

    That should be greater than or equal to for items [more recent] than (5 days ago)

  2. Gravatar

    Hi Mirjam,

    First of all nice post I'm looking to do something simular. Create a web part which lists updated documents from the last tywo weeks and this for all my site collections...

    Do you have some code examples for the above or how you coded your birthday calendar?

    Thanks in advance!
    Thumbs up!

  3. Gravatar

    Hi Mirjam,

    Nice blog :).

    But i have a question.

    I have a search page with 2 webparts: refinement panel and my custom webpart.
    In my custom webpart i'm using FullTextSqlQuery.

    When I perform a search i can find documents and items but my refinement panel stays empty.

    Do you have an idea how i can fix this issue ?

    With regards

  4. Gravatar

    Hi Timmy,

    Is your web part using the SharedQueryManager? That's the class that allows for the different web parts to communicate with each other.


  5. Gravatar

    hi mirjam,

    I developed a custom web part using full text query and bind the results to grid.
    Now my requirement is how can i use the same full text query to refinement panel.
    I want to custom results using full text query in grid and refinement panel.

  6. Gravatar

    Hi Shaik,

    If you use the FullTextSqlQuery functionality you have to build the logic for using the same results in multiple search web parts by yourself.
    If you can change your solution to use the SharedQueryManager though the SharedQueryManager will take care of this for you. It does mean that you are a bit more limited in terms of what you can customize, but you're still pretty flexible.
    More info:

    Hope that helps.

    Kind regards,

  7. Gravatar

    Great! You solved my problem, thank you!

  8. Gravatar

    A small yet notable release, for Winter 2016 Randolph Engineering drops their oakley replica sunglasses technically charged blue lenses. More specifically Blue glass AR Hydrophobic lenses. Available both in their iconic aviator and the new moncler outlet P3 Shadow frame styles. Studies have shown blue lenses to have a calming effect on the wearer, the grey blue ray ban outlet tint enhances contours around objects and reduces glare. This ease in viewing experience seems to invoke less stress on the eye for longer time wear. Additionally a hydrophobic coating was added to the blue glass lenses. Hydrophobic simply defined cheap christian louboutin means afraid of water. The specially designed coating facilitates the run-off of water droplets from the lens surface whether from sweat or from an aqueous environment or in the rain.

  9. Gravatar

    Solutions de recherche personnalisées pour SharePoint 2010 - FullTextSqlQuery
    sac hermes pas cher


Leave a Reply


Please add 5 and 6 and type the answer here: