Content Queries Using SharePoint Enterprise Search API

February 15 2010 74 comments

In an earlier post to SharePoint Blues Juho wrote about the challenge of – how to find anything from the sea of content. One way of solving this issue is to use SharePoint Enterprise Search API and the FullTextSqlQuery class. This class allows you to query SharePoint content using SQL syntax. FullTextSqlQuery queries always query from SCOPE(). In the where clause you set the target scope. Here is an example how the query is formed:

SELECT Title FROM SCOPE() WHERE "scope" = 'All Sites'

As you can see the syntax is SQL and this makes querying content pretty easy. At this point I want to point out that you can only query data that has been indexed by SharePoint so you need to crawl content before you can get any results. This is also a thing that might make using FullTextSqlQuery class impossible – the content isn’t realtime because it needs to be indexed first.

For the following example I created two SharePoint pages. One with a title ‘Example’ and second with a title ‘Example of FullTextSqlQuery’. Below is the example code in which I query content from All Sites scope and where Title field must contain the word ‘Example’:

var query = new FullTextSqlQuery(SPContext.Current.Site)
    QueryText = "SELECT Title FROM SCOPE() WHERE \"scope\" = 'All Sites'
        AND CONTAINS(Title, 'Example')"
    ResultTypes = ResultType.RelevantResults
var queryResults = query.Execute();
var queryResultsTable = queryResults[ResultType.RelevantResults];
var results = new DataTable();
results.Load(queryResultsTable, LoadOption.OverwriteChanges);

After running the code the results DataTable contains both of the pages I had created earlier. In the query I used predicate CONTAINS and with that I was able to look for word ‘Example’ in the Title field. FullTextSqlQuery uses managed properties in its queries and in the example query Title is an managed property. You can also easily create custom managed properties from your Shared Services Provider under Search Settings.

I think using FullTextSqlQuery is one way to find essential information from the sea of content.

Couple more things to add:

- Remember to use following using statement to make FullTextSqlQuery work:

using Microsoft.Office.Server.Search.Query;

- I found out that boolean fields in where clause behave bit strangely. I couldn’t get =true working, but I found a workaround for this. You can use !=false instead of =true

Popularity: 100% [?]

74 comments to “Content Queries Using SharePoint Enterprise Search API”

  1. [...] Content Queries Using SharePoint Enterprise Search API (SharePoint Blues)In an earlier post to SharePoint Blues Juho wrote about the challenge of – how to find anything from the sea of content. One way of solving this issue is to use SharePoint Enterprise Search API and the FullTextSqlQuery class. This class allows you to query SharePoint content using SQL syntax. FullTextSqlQuery queries always query from SCOPE(). In the where clause you set the target scope. Here is an example how the query is formed: [...]

  2. Derek says:


    I’m using a fulltextsqlquery to successfully return results, but one thing I require is similar functionality to the normal keyword search. I’m indexing the HTML of my sharepoint pages so that the user can search for text on any rendered page.

    I’d like to return results where you’d find values in the HitHighlightedSummary, but using the wildcard functionality of the fulltextsqlquery syntax.

    Which column in the results can I use in my “like” statement to get this to work? And is this even possible?

    …AND ???? LIKE ‘%ice%’

  3. Aapo Laakkonen says:


    You can use CONTAINS(‘*ice*’) or CONTAINS(‘*’, ‘*ice*’) or CONTAINS(‘ALL’, ‘*ice*’), I think, but I have not tried this. Please let us know if it works.

  4. Aapo Laakkonen says:


    Oh, I didn’t answer directly to your question, have you checked these:

  5. Faith says:

    I would like to search content on my sharepoint sites from another site (non-sharepoint) and process the result to have control on how it gets rendered on my site. Does sharepint provide API to do that? If so, would anybody give me any clues? Thanks!!!

  6. [...] scope of custom queries. We’ve had our CrossListQueryCache’s, SPSiteDataQueries, the search API along for some time now and with these libraries and techniques it hasn’t been a hard task to [...]

  7. Lou says:

    queryText.Append(“SELECT AccountName “);
    queryText.Append(“FROM Scope() “);
    queryText.Append(“WHERE \”scope\” = ‘People’ “);
    queryText.Append(“AND \”Roles\” = ‘” + role + “‘ “);

    returns the account name for me but the following does not return the roles for the given account name!

    queryText.Append(“SELECT AccountName, Roles “);
    queryText.Append(“FROM Scope() “);
    queryText.Append(“WHERE \”scope\” = ‘People’ “);
    queryText.Append(“AND \”AccountName\” = ‘” + user + “‘ “);

    Seems the Roles is empty! Any idea? The Roles is a custom property that gets its value from a Managed Metadata.

  8. Janaiah says:

    Hi, This is very good information. I have tried your code and its working perfect for Farm Administrator but when try with normal users (Site Owner, Contributor etc) not returning results. Could you help me in this.
    I would like to get site collections for which current user have access.


  9. Quality content is the main to attract the users to pay
    a visit the website, that’s what this web site is providing.

  10. Samuels (Eminem) “Nothin’ On You” — Philip Lawrence,
    Ari Levine, Bruno Mars & Bobby Simmons Jr. –LINK REMOVED
    –>Grammy Nominations 2011<. Juni, als Bombenanschläge in Kathmandu und anderen Städten Nepals acht Tote und 22 Verletzte forderten.

  11. I comment each time I like a post oon a website or if I have something
    to valuable to contribute to the conversation. It is a esult of the fire displayed iin the article I looked at.

    And on this post Content Querie Using SharePoint Enterprise Search API
    | SharePoint Blues. I was actually moved enough to drop a leave a responsea
    response ;-) I actually ddo have a few quhestions for you if you
    tend not too mind. Is it just mee or does it appear like a
    ffew of the responses come across like coming from brain dead visitors?

    :-P And, if you are writing on othher sites, I’d like to keep up with antthing new you have to post.
    Could you list all of all your shared pages likke your linkedin profile,
    Facebook page or twitter feed?

  12. Rose essence is not irritating and is suitable for my skin.
    Similarly, the skin of patients with severe acne. Some women with mild acne,
    but it may take 2-3weeks for your skin, so ease into a regimen that
    includes the two to avoid redness and peeling. What makes
    human it even more parched. If you run out of nail polish remover but I don’t like scented aftershave balms as I do in terms of diet.

  13. Ollie says:

    Please let me know if you’re looking for a article writer for your blog.
    You have some really great articles and I believe I would be a good asset.
    If you ever want to take some of the load off, I’d love to write some material for your blog in exchange
    for a link back to mine. Please send me an e-mail if interested.

    Many thanks!

  14. Hey There. I found your blog using msn. This is a really well written article.
    I’ll make sure to bookmark it and come back to read
    more of your useful info. Thanks for the post. I will certainly comeback.

  15. Heya! I realize this is somewhat off-topic however I needed to ask.

    Does running a well-established website like yours require a lot of work?
    I’m completely new to writing a blog however I do write in my diary every day.
    I’d like to start a blog so I will be able to share my personal experience and thoughts online.
    Please let me know if you have any recommendations or tips for brand
    new aspiring blog owners. Thankyou!

  16. Aundrea says:

    I read a lot of interesting articles here. Probably you spend a lot of time
    writing, i know how to save you a lot of work, there is an online tool that creates readable,
    SEO friendly posts in minutes, just search in google – laranitas free content source

  17. I’m gone to tell my little brother, that he should also visit this webpage on regular basis to get updated from newest news update.

  18. WOW just what I was looking for. Came here by searching for advice

  19. Undeniably believe that which you stated. Your favorite
    reason seemed to be on the net the easiest thing to be
    aware of. I say to you, I definitely get irked while people think about worries that they just don’t know about.
    You managed to hit the nail upon the top as well as defined out the whole thing without having side-effects ,
    people can take a signal. Will probably be back to get more.

  20. Greetings other online sites performs good in my situation yet
    your site is loading slowly which had taken approximately a few minutes to be able to load,
    I am not sure if it’s my problem perhaps website problems.
    Nevertheless, I have to appreciate you for adding excellent article.
    Perhaps it has become helpful to many individuals
    . This is definitely incredibly good what you actually have
    concluded and wish to discover even more interesting posts from you.
    I already have your site saved to my bookmarks to see blog you

  21. Amazing blog! Do you have any tips and hints for aspiring writers?
    I’m hoping to start my own blog soon but I’m a little lost on everything.

    Would you recommend starting with a free platform
    like WordPress or go for a paid option? There are so many options out there that I’m totally confused ..
    Any ideas? Cheers!

    My page online dead trigger 2 hack

  22. I’d definitely recommend the Murad and Mario Badescu option of goods for acne.

  23. Maurice says:

    Very good info. Lucky me I recently found your blog byy chance
    (stumbleupon). I have bbook marked it for later!

  24. Wow because this is excellent job! Congrats and keep it up.|

  25. Kristen says:

    I’ve been surfing on-line more than 3 hours lately, but I never
    found any fascinating article like yours. It’s pretty value enough for me.

    Personally, if all site owners and bloggers made just right content as you did, the internet can be much more
    helpful than ever before.

  26. nba 2k16 mt says:

    Many thanks, this site is extremely useful

  27. nba 2k16 mt says:

    Great internet site! It looks really professional! Keep up the great work!

  28. What I have found is that I can directly control
    all facets of my twitter account. It also holds an unparallel cultural heritage and tradition. There are a lot of factor to learning how to sell well on Twitter and there is still a
    lot of research taking place to find the best methods.

  29. madden 17 says:

    Really desired to express I am grateful that i came on your web page!.

  30. madden 17 says:

    Truly… such a advantageous internet site

  31. Monte says:

    Hi there to every body, it’s my first pay a visit of this website; this web site
    includes awesome and genuinely good stuff in support of visitors.

  32. Tracee says:

    I got this web site from my pal who shared with me
    concerning this web page and at the moment this time I am visiting this web
    site and reading very informative posts at this time.

    Feel free to surf to my webpage … club penguin walkthrough
    spy and seek (Tracee)

  33. I pay a visit daily a few web sites and information sites to read articles, however this weblog provides feature
    based posts.

  34. Jarred Synnot says:


    SEO Link building is a process that requires a lot of time fo
    If you aren’t using SEO software then you will know the amount of work load involved in creating accounts, confirming emails and submitting your contents to thousands of websites in proper time and completely automated.

    With THIS SOFTWARE the link submission process will be the easiest task and completely automated, you will be able to build unlimited number of links and increase traffic to your websites which will lead to a higher number of customers and much more sales for you.
    With the best user interface ever, you just need to have simple software knowledge and you will easily be able to make your own SEO link building campaigns.

    The best SEO software you will ever own, and we can confidently say that there is no other software on the market that can compete with such intelligent and fully automatic features.
    The friendly user interface, smart tools and the simplicity of the tasks are making THIS SOFTWARE the best tool on the market.



  35. After looking at a number of the blog posts on your blog, I honestly like your way of writing a blog.
    I saved as a favorite it to my bookmark webpage list and
    will be checking back soon. Take a look at my website as well and
    let me know your opinion.

  36. hi!,I like your writing so so much! proportion we communicate extra
    about your post on AOL? I require a specialist
    in this area to unravel my problem. Maybe that’s you! Having a look
    ahead to see you.

  37. I’d like to thank you for the efforts you have put in writing this site.
    I’m hoping to see the same high-grade content from you in the
    future as well. In truth, your creative writing abilities has motivated me to get my own, personal
    site now ;)

  38. If you desire to take a great deal from this post then you have to apply such strategies to your won blog.

  39. Nice post. I was checking constantly this blog and I’m inspired!
    Extremely helpful information specifically the ultimate phase :
    ) I handle such information a lot. I used to be seeking this
    particular info for a very long time. Thank you and good luck.

  40. That is very interesting, You’re an excessively skilled blogger.
    I have joined your rss feed and stay up for in search of extra of your excellent post.
    Also, I’ve shared your website in my social networks

  41. I’m gone to convey my little brother, that he should also go to see this blog on regular basis to get updated from newest information.

  42. ios app says:

    I don’t know whether it’s just me or if perhaps everyone else experiencing problems with your website.
    It appears as if some of the written text in your posts are running off the screen. Can someone else please comment and let me know if this is happening to them as
    well? This might be a issue with my browser
    because I’ve had this happen previously. Thank you

  43. An outstanding share! I have just forwarded this onto a friend who was conducting a little
    research on this. And he actually ordered me breakfast due to the
    fact that I discovered it for him… lol. So allow me to reword this….
    Thanks for the meal!! But yeah, thanks for spending the time to discuss this topic here on your
    web site.

  44. I was wondering if you ever thought of changing the page
    layout of your site? Its very well written; I love what
    youve got to say. But maybe you could a little more in the way of content so people
    could connect with it better. Youve got an awful lot of text for only having one or two
    pictures. Maybe you could space it out better?

  45. This article presents clear idea for the new users of blogging, that truly
    how to do blogging and site-building.

  46. best app says:

    Thank you for every other excellent article.
    The place else may anyone get that kind of information in such a perfect way of writing?
    I’ve a presentation subsequent week, and I am on the look for such information.

  47. Baru sempet baca postingan ini hari ini, dan ternyata kontennya menarik banget.
    Kayaknya aku akan sering mampir di mari, semangat
    update terus kontennya ya min.

  48. Thanks for finally talking about > Content Queries Using SharePoint Enterprise Search API |
    SharePoint Blues < Loved it!

  49. It’s actually very complex in this busy life to listen news on Television,
    thus I only use the web for that purpose, and get the hottest information.

  50. Remarkable! Its really awesome article, I have got much clear idea regarding from this piece of writing.

Leave a Reply