SharePoint 2007: BDC - Enabling Search on business data.

Posted on 5/17/2007 @ 1:45 PM in #SharePoint by | Feedback | 26066 views

SharePoint 2007: BDC - The Business Data Catalog

This post is in continuation to a series of blogposts I put up on BDC - the Business Data Catalog.

Table of Contents:

  • Introduction
  • In-built Webparts
  • Lists with Business data type columns.
  • Search <-- You are here
  • User Profiles
  • Other custom applications targeting a common runtime object model.
  • Making it easier to author XML Applications: BDCMetaMan

    In my series on BDC, I just got done describing a complete business data application definition, that lets you import data from Northwind.Customers, and lets you have custom actions, and lets you work with a custom defined association that lets you pull in Orders for the given customer.

  • WHEW! (You can tell, I'm getting tired of all this typing).

    Now, starts the real fun. I have a somewhat sophisticated line of business application definition setup. My next step is to integrate it with whatever else sharepoint offers, and create a compelling solution for Northwind traders (Damn I wish northwind traders paid me for all this work). In this blogpost, I am going to create a sophisticated search engine for NorthWind traders customers.

    To enable search on business data, you have to go through the following steps -

    So let us perform these steps one by one.

    1. Modify the XML Goo to enable search.

    My intent is to make "Customers" searchable. So I am going to add an IDEnumerator kind of method instance in a new method as shown below -

    <!-- A Method for enabling search.-->

    <Method Name="CustomerIDEnumerator">


        <Property Name="RdbCommandText" Type="System.String">Select CustomerID from Customers</Property>

        <Property Name="RdbCommandType" Type="System.String">Text</Property>



        <Parameter Name="CustomerIDs" Direction="Return">

          <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Customers">


              <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Customer">


                  <TypeDescriptor TypeName="System.String" IdentifierName="CustomerID" Name="CustomerID">


                      <LocalizedDisplayName LCID="1033">ID</LocalizedDisplayName>










        <MethodInstance Name="CustomerIDEnumeratorInstance" Type="IdEnumerator" ReturnParameterName="CustomerIDs"/>




    This method returns unique IDs for customers. MOSS search will query for IDs using this method first, and then it uses the SpecificFinder method to get other metadata on the customer based on these IDs. This will allow for a full crawl. If you wish to enable incremental crawl as well, you need to also send back a field telling MOSS the last time this customer was seen/changed.

    2. Create a content source and perform a content crawl.
    3. Authentication will be a concern here.

    In order to create a content source,

    • Go to SSP -> Search --> Search Settings and click on "Content Sources and Crawl Schedules".
    • Create a new content source
    • Specify various information to crawl NorthWindTraders. Here is how my screen looks -
    • Note that I did not request a start full crawl. I was smarter than that, because if I issue a full crawl, I know it will simply not work. Why?

    This brings me to the second part - Authentication. I am using "PassThrough" authentication in the above BDC App. This means, I am logged in as "MyDomain\Sahil", and "MyDomain\Sahil" has access to the database NorthWind. Also, Sahil has access to the BDC application. So when "Sahil" tries accessing the BDC, it works. But, when Search (which is running as "MySharePointMachine\Local Service" tries accessing BDC, it bombs).

    So I need to fix it. Here is how -

    - Create a new account for search.
    - Change the default content access account to that account. This can be done under search settings.
    - Change search crawler account to that account. This can be done under operations\services on the server\office sharepoint server search
    - Give this account access to NorthwindTraders
    - Make this account have edit, execute & select permissions on the Northwindtraders business data catalog. Copy permissions to appropriate decendants.

    The other option I have is to use a different kind of authentication (but that has it's own ramifications).


    Now wait for a minute because - a) I'm tired, b) The metadata is cached for a minute, let it go through to all SSP machines.

    After having waited for a minute, :), start the crawl on CustomersBDC content source you defined a moment back. I like to run SQL Server Profiler in my test environment, just to make sure that the queries are actually getting passed. You could always look at SharePoint logs also for errors, but this is just a lot easier. :-)

    If you did everything right (and waited a minute as I recommended), you should see the content source crawling when you request for a full crawl as shown below -

    .. and your SQL Server profiler should show the relevant queries being issued -

    4. Add MetaDataProperties and Crawled Properties

    This is optional/icing on the cake scenario. If interested in this, Read this blogpost I wrote on Search Scopes + MetaDataProperties.

    5. Create appropriate search scopes -

    • In the SSP under search settings, under the "Scopes" area, click on "View Scopes"
    • Click on "New Scope", call it CustomersBDC
    • Click on "Add Rules" where it says "Empty - Add Rules" on the newly added scope, CustomerBDC.
    • Specify a Content Source as shown below -
    • You should see scopes listed as below -
    • Go ahead and issue an update (I just don't feel like waiting another 9 minutes). This can be done from the main search settings page.
    • After having issue an update, you will see that the search scope Customer BDC now has 93 items populated in it.
    • Your search is now ready to use.

    To use the search,

    • Go to your favorite MOSS site.
    • Go to site settings, search scopes, and change Customers BDC from unused scopes to Display group: Search Dropdown.
    • Find a page which you can customize with webparts, I'm gonna use default.aspx
    • Add the following webparts - Search Box, Search Core Results, Search Paging, Search Statistics, and Search Summary. Order them like this -
    • Change the "Target search results page URL" for the Search box to http://moss2007/default.aspx (or whatever the above page URL is).
    • Change "Results Per Page" for "Search Core Results" to 5.
    • Get out of Edit mode.
    • .. and issue a search for "Londn" (note to intentional misspelling). You should see an output like this -
    • Click on "London" to issue a search on the keyword "London" - you should see an output like this -

    NEAT !!!!

    I don't know about you, but I find this truly impressive. The search results, based on the Northwind database, are not fully searchable, but they also support custom alerts, and RSS Feeds.

    Can't beat that with a pocket pc, now can ya!

    Okay, I'm tired of typing about BDC, I'll cover the rest of the 2 topics - User Profiles and Custom code on BDC next time.

    Sound off but keep it civil:

    Older comments..

    On 9/20/2007 10:48:18 PM Tim said ..

    Thanks for the useful blog. I have been modifying the BDC search results xsl, and was wondering if you know how to get at custom actions in the search results. In other words ... I can get the value of the default action using {$url}, but is there a way to get at the other actions in the bdc entity. I guess I want to be able to byass having to go to the bdc profile page.



    On 2/6/2008 4:08:24 AM Moim Hossain said ..
    Hi Desai, This entry is awesome. neatly explained everything, and I was able to see light following the steps you advised. I spent the entire yesterday to make a search work, but I was not able to do so- following bunch of blog entires and articles. I was facing security related issues.

    Anyway, I am feeling lucky that I got this link today morning and it just work for a single kick!

    Thanks much!

    On 2/6/2008 6:11:37 AM Sahil Malik said ..
    Desai? Who the fudge is desai?

    On 2/20/2008 11:37:26 AM Vaibhav said ..
    Great work Sahil. The information has been very well laid out and good for people like me who are new to BDC.

    You are on my IE favorites now :)

    On 2/20/2008 3:54:25 PM Sahil Malik said ..
    Thanks !! But just IE favorites? You haven't heard my jokes yet :P

    On 5/17/2008 6:44:38 AM Simon said ..
    Sahil, thanks for this article/post - Helped me get round a problem I had with enabling BDC search which turned out to be permissions related. I've linked back to here from my blog.

    On 7/1/2008 9:31:05 PM sandeep nahta said ..
    very nice article .. covers everything for BDC except the webservice usage ?

    On 7/23/2008 11:44:55 AM Brian said ..
    Thanks for the posts, Sahil. Any idea how to configure search across associated BDC entities so that the results returned understand the relationship between the entities? For example, if I have a Customer entity and a related Order entity, and I have configured search to crawl both, can I search on information in the Order entity but have the result set return the associated Customer?

    On 9/22/2008 11:15:18 PM Vivek said ..
    I did all the steps as mentioned till # 3 (Crawler set up). But when I fire up the crawler, I am getting the following error message in the crawler log:

    The password for the content access account cannot be decrypted because it was stored with different credentials. Re-type the password for the account used to crawl this content.

    I've verified that the Office Search service is running under the configured search account and that account has been added to the Northwind Database users and has all the privileges.

    Any suggestion would be appreciated.

    On 9/22/2008 11:35:25 PM Vivek said ..
    I did all the steps as mentioned till # 3 (Crawler set up). But when I fire up the crawler, I am getting the following error message in the crawler log:

    The password for the content access account cannot be decrypted because it was stored with different credentials. Re-type the password for the account used to crawl this content.

    I was able to solve the problem by going back to the Search Settings page under SSP and then reentering the credentials for the content crawler account.

    Well the error was pretty self explanatory but the cells in my head took some time to understand that.

    Anyways, an excellent article.

    On 10/8/2008 11:57:14 PM Hitesh said ..
    Good on you! Thanks for posting such a useful article.

    On 10/15/2008 4:02:47 AM Craig said ..
    Thanks for some brilliant BDC articles. I'm busy implementing a large-scale BDC integration project and been bumping into some interesting issues. The biggest problem at the moment relates to search... One of the entities is clients of which this institution has over 200 000. I want to set up a weekly crawl but to have a single ID enumerator method that returns that many rows a in a single web service call is far from ideal - even if it's only IDs. Any way to to get the BDC to handle paged results??

    On 3/13/2009 2:26:22 PM Mr. A said ..
    Hi, you state - This will allow for a full crawl. If you wish to enable incremental crawl as well, you need to also send back a field telling MOSS the last time this customer was seen/changed. -

    How about adding to your post how this is done, would be fantastic to know.

    On 3/28/2009 1:45:42 AM Greg said ..
    I'm interested to know how to set properties in the xml file which give a better display in the search results. Fore example, setting teh title and suppressing all the fields from display

    On 5/7/2009 2:44:33 AM Mahi said ..
    Awesome post. I used this to create a custom bdc for my db. Everything works except that i get something added to the results url which makes the url not working. I am not sure whether it is an issue with the bdc definition or the search in MOSS.


    In the last portion of the URL, FoundCD=a1b2c3 is repeated. If we manually remove it, then it works.

    Please suggest.



    On 6/17/2009 5:39:19 AM Thomas Richards said ..
    I was just thinking about business data and you've really helped out. Thanks!

    On 10/14/2009 4:42:30 AM Ashraf siddiqui said ..
    Thanks Sahil for your support extended..

    Same here for any query please revert back at

    Thank's and Best Regard's


    On 5/3/2010 11:29:38 AM Keith said ..
    Hi Sahil,

    Am trying to get a feel for how scalable the BDC is. Is it feasible, for instance, that a small MOSS farm can use a BDC connected to multple LOB databases, with 30 million records, and still run without crashing? Does the BDC created a file for those indexed records, and if so how large would it be? Is it stored on the SQL back end db? For a large set of files, would this require tens of gigabytes of extra storage space on the back end?