Archive for SharePoint 2013 Search

SharePoint 2013 Search – How to Crawl Large External Data using BCS/WCF Service.

Scenario:

I had a hard time finding an end to end solution to this problem. We had to index external content using SharePoint search via BCS. There are plenty of blogs that described how to create an External Content Type, how to create a SharePoint WCF service, how to create a BCS Service, and how to use that connection to crawl content using SharePoint Search. The issue arose, where the number of rows returned went above some thresholds that SharePoint had when it comes to WCF Services. Say you have a data source that has millions of rows… How can you safely crawl such large data without:

  1. bringing the Search Server to a “crawl” (no pun intended).
  2. Putting undo stress on the SQL server and network lines for bringing such a large data over the wire.

More than likely you will run into error messages such as the following in the SharePoint Logs.

  • Timeout expired. BDC Runtime Object Model has throttled the response. The response from the service took more than ‘180000’ milliseconds. The limit can be changed via the “Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.
  • WCF Service Connector has throttled the response. The response from the WCF service contains more than ‘3000000’ bytes. The maximum amount of data that can be read through WCF Service Connector is ‘3000000’ bytes. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.
  • Web Service Connector has throttled the response. The response from the web service contains more than ‘3000000’ bytes. The maximum amount of data that can be read through Web Service Connector is ‘3000000’ bytes. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.
  • Opening of a new connection is throttled. Maximum enabled connections ‘200’ for proxy are already been opened.
  • Database response throttled. Maximum number of rows that can be read through the database system utility is 2000.

There were some proposed solutions, such as increasing the default throttling by using a set of PowerShell commands. However, when it comes to large data, you’re still going to run into trouble.

Solution:

Implement Paging in your WCF service.

  1. Create a Method in WCF Service to call the database using some parameter to return row limits.
  2. Create a List Read Function for the WCF using SharePoint Designer.
  3. Set a Filter for that method in SharePoint Designer.
  4. Crawl.

Create a Method in WCF Service to call the database using some parameter to return row limits.

I will not get into the details of how to create a SharePoint WCF Service. We’re going to assume you know how to do that. We’re going to implement a method in the main WCF Service Class that returns IEnumerable of type “SPCrewData” and pass a parameter named PageNum.

 

In the above mentioned method, two things need to be handled:

       1.Get the total count of the items to be returns from sql.
       2. Page through by using that number to do a comparison against in SQL Query.

 

Get the total count of the items to be returns from sql.

    To return count simply use the select COUNT(*) function in sql. If your query is fairly simple then use something like this:

   

If you have a fairly complicated sql query then simply wrap that sql statement around your complicated query and also pass a ROWNUM column by using a ROW_NUMBER() sql function

   

Page through by using that number to do a comparison against in SQL Query

Here is the complete Code. 

//This method will page through items a 1000 at a time. 

public IEnumerable getAllItemsPaged(int PageNum)
{
List itemList = new List();
string SqlQueryTotalCountStr =
@"SELECT COUNT(*) AS TotalCount
FROM ( SELECT *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) "
;
//return the total count of the results e.g. 35000
int count = 0;
try
{
using (SqlConnection conn = new SqlConnection(""))
{
using (SqlCommand sqlComm = new SqlCommand(SqlQueryTotalCountStr, conn))
{
try
{
conn.Open();
using (SqlDataReader r = sqlComm.ExecuteReader())
{
itemList = new List();
while (r.Read())
{
count = Convert.ToInt32(r["TotalCount"]);
}
}
}
catch
{
}
}
}
}
catch
{
}
//if rowNumb is <= TotalCount then perform this
if ((1000 * PageNum) <= count && count > 0)
{
//if rownum +1001 is greater than total count (e.g. 35000)
//then simply use totalcount - (rownum +1) as the upper limit.
//this matter during the tail end of the paging. The if else is there to handle the last set of
                ​//items to be indexed.   

int upperlimit = 0;
if ((1000 * PageNum) + 1001 >= count) upperlimit = count - ((1000 * PageNum) + 1);
else upperlimit = (1000 * PageNum) + 1001;
string SqlQueryStr = @"SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= " + ((1000 * RowNum) + 1) +"
AND RowNum < "+ upperlimit+"
ORDER BY RowNum"
;
try
{
using (SqlConnection conn = new SqlConnection(""))
{
using (SqlCommand sqlComm = new SqlCommand(SqlQueryStr, conn))
{
try
{
conn.Open();
using (SqlDataReader r = sqlComm.ExecuteReader())
{
itemList = new List();
while (r.Read())
{
SPCrewData itemObj = new SPCrewData();
itemObj.Author = r["Author"].ToString();
itemObj.BlogID = Convert.ToInt32(r["BlogId"].ToString());
itemObj.Description = r["Description"].ToString();
itemObj.Title = r["Title"].ToString();

itemList.Add(itemObj);
}
}
}
catch
{
}
finally
{
}
}//END USING
}//END USING
}
catch
{
}
finally
{
}
}
return itemList;

}

Set a Filter for that method in SharePoint Designer

   In SharePoint Designer, you will be Creating the External Content types, and mapping it to the WCF Service that you created. When It comes tocreating Operations for the service, right click in the Data Source Explorer in SharePoint Designer and select the “New Read List Operation”

 

 

  

 

Click Next where you will see the page displaying the Input Parameter

  

Click the “Click to Add” link which will take you to the “Filter Configuration” dialogue.

  

Select a “New Filter”

Choose Page Number as the filter type.

Choose “PageNum” as the Filter Field.

 

Here is what will happen. When Search calls this Method, it will call your method by initially passing a default PageNum value of 0. Your method will execute, and search will index those results. It will then call the method again, however, this time, it will increment the PageNum Value to 1, Execute, index, call method again, increment PageNum Value to 2, …etc,etc….

Click Finish.

Crawl

  • Create your Content Source in SharePoint Search Service Application as a Business Data Connectivity.      
  •      
  • Point it the WCF Service that you created .
  • Hit OK.                                                                                                    

      

     

  • Run a full crawl.
  • You should get all the items in the sql back end indexed via BCS.

 

Hope you found this blog useful.

SharePoint 2013 – The new “Preview Feature” in Search Results

In SharePoint 2013 Search has an Incredible new feature where for each search result that gets displayed on the search results page users can now “Hover Over” the result to view a Preview box for that Item.

Here is the quick Screen about the “Hover Panel” or the Preview box.

Some of the Features of this Hover Panel dialog box are –

* Hover panel has a high-fidelity Web Application Viewer or Preview that is available for all Office documents saved to SharePoint. These Interactive previews are big enough to give you a idea of the structure, contents, and styling of the document you’re hovering over.

* The hover panel contains rich metadata that enables users to investigate a result more thoroughly, without having to click through and load the document. See “Last Modified” and “Contributors include” in the above screen.

* In addition to the Metadata it also displays Section headings and slide titles that are inside of a document, under the heading “Take a look inside. These are called “deep links” and you can see them in the above hover panel. These headings and titles are links—clicking through will take you directly to that section (or slide) in the document.

* The bottom of the hover panel contains a list of actions that may be performed on a result. Now you can follow the document, Edit the document, View the document’s library, View duplicates, and\or send the document in an email message right from the results page. These actions are also completely customizable, so your results can be more actionable.