Searching SQL from SharePoint 2010 search UI

In SharePoint 2010 there has been many improvements on how you can access external data and allow for it to be displayed to the user using SharePoint 21 UI. Search federation is one of those features.

Essentially on your search results page you can have few Search Core Results web parts where each can be set to return data from various Federated Locations. In this example we’ll take a look how you can configure local SQL running Adventure Works to be one of those Federated Locations.

In your SharePoint Central Administration select Search Service Application and then click Manage Federated Locations on the left.

You will be presented with an interface similar to below:

Here you can see few existing federation locations which you can export. In my example I’m going to export Internet Search Results federation location and rename the resulting OSDX file to SQLResult.osdx.

Next we’re going modify the OSDX file to support search in SQL Adventure Works.

Open OSDX file in notepad and modify below respective items in the XML file:

<ShortName>Adventure Works Results</ShortName>
 <Description>This location provides Adventure Works results.
location">1.</Version>  <ConnectionUrlTemplate xmlns="http://schemas.microsoft.com/
location">1.</Version>  <ConnectionUrlTemplate xmlns="http://schemas.microsoft.com/
27/location">SQLFederation.SQLServerLocationRuntime, SQLFederation, 
Version=1., Culture=neutral, PublicKeyToken=3afddfad18ff4ff</LocationType>  
<Version xmlns="http://schemas.microsoft.com/Search/27/
location">1.</Version>  <ConnectionUrlTemplate xmlns="http://schemas.microsoft.com/
Search/27/location">mssql://Data Source=.;Initial Catalog=AdventureWorksLT28; 
UserId=mydomain\yar;Password=mypassword;?q=Select TOP 1 Name as title 
From [SalesLT].[vProductAndDescription]</ConnectionUrlTemplate>

We’ll modify the query inside ConnectionUrlTemplate at this time ensure you have a proper user name and password to your SQL which I assume already has Adventure Works installed.

Now, you can import the newly modified file using the import command. After the import succeeds you can review what changes now have been added to the template and save the federation location.

At this time you can go to your search center and pick Search Core Results web part that you will use for your Adventure Works results and access the properties of the web part as shown below:

You can pick your new federated location; however, when you launch new search you won`t get any results just yet. In my next article I`ll show you how you can actually retrieve results from the Adventure Works DB and display them here.

Stay tuned ..

About the author: Yaroslav Pentsarskyy