Displaying SSRS Reports in External Applications using an iframe – SQL Server 2016

Problem Statement

After upgrading the SQL Server Reporting Service to 2016, the SSRS reports embedded in different applications including SharePoint pages using an iframe tag or page viewer web parts were stopped rendering.




Workaround Tried

I tried to fix this issue by adding the following entries in the rsreportserver.config but no luck.

report config file.png

The rsreportserver.config file is installed in the following location by default:

SharePoint Mode

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

Native Mode

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer


Added rs:Embed=true as a query parameter at the end of the report URL and this fixed the issue and reports started working properly.


Adding SharePoint Site as a Search Provider in Internet Explorer

AddSearchProvider was introduced in Windows Internet Explorer 7.

This method opens a dialog box that enables the user to add the provider to their registry, and optionally set it as the default search provider. The maximum number of search providers that can be installed is 200.

The sUrl parameter allows http:, https:, or ftp: protocol schemes only. Additionally, the URL must be in a security zone that permits downloading.

Search providers must use the HTTP GET request method; the POST request method is not supported.

Syntax of “Provider.xml” must be of valid XML format. Be sure to encode all characters in the query string. Particularly, escaping “&” to “&”.

The query string must contain “q={searchTerms}” somewhere within the query string. When Internet Explorer 8 navigates to the provider to get search results, “{searchTerms}” will be replaced by the query string that the user typed into the Instant Search box.

To add SharePoint Search Site as a Search Provider in Internet Explorer such bing or google we have to user the below syntax


Here sUrl is a string that specifies an absolute or relative URL to the OpenSearch Description file for the search provider.

The following code will add the search provider on click of the button

<INPUT TYPE=”button” VALUE=”SPRIDER Search Provider”   onClick=’window.external.AddSearchProvider(“/_layouts/15/SPRIDER/XML/SPRIDER_IE_Provider.xml”);’>

This can be advertised automatically by embedding this file on the master page or webpart zone with the help of CEWP as shown below

<link title=”My Provider Name” rel=”search” type=”application/opensearchdescription+xml” href=”(“/_layouts/15/SPRIDER/XML/SPRIDER_IE_Provider.xml”>

Sample OpenSearch Description file content

<?xml version=”1.0″ encoding=”UTF-8″?>

<OpenSearchDescription xmlns=”http://a9.com/-/spec/opensearch/1.1/”>

<ShortName>SPRIDER Search </ShortName>

<Description> SPRIDER Search provider for Internet Explorer</Description>


<Url type=”text/html” template=”http://sprider/Pages/results.aspx?k={searchTerms}”/>

<Url type=”application/x-suggestions+xml” template=”http:// sprider/_layouts/15/amat/SuggestionProvider.aspx?k={searchTerms}”/>

<Image height=”16″ width=”16″ type=”image/x-icon”>http://sprider/_layouts/15/SPRIDER/Images/spriderfavicon.ico</Image>


Here I have used the customized Suggestion response page (SuggestionProvider.aspx) to send empty response message when no suggestions found.

SharePoint 2010 not showing results – Files with big size

Problem Statement:

Our current search does not seem to include older Office versions (e.g. ppt) in SharePoint 2010 Search results.  Why did we limit this?


When I received the above mentioned problem statement I started exploring the below:

  • Is there a problem in crawl / any restrictions configured to specific file types – Test passed
  • Is Browser Locale setting in the Search Result Core webpart trimming the results specific to some location – Test passed
  • Is Remove Duplicate Results settings  in the Search Result Core webpart causing any issue– Test passed
  • Compared the file size between the files which are appearing the search results and the issue causing file – Test partially failed, the file of the disappearing files are always bigger than the files which are appearing in the search results.  

 I stopped here and started exploring is there a limitation in the crawler settings to crawl the file contents with big size and found that SharePoint is by default limited to crawl file contents which are less than 16 MB.

So SharePoint when it crawls the files from the document library or list, if the file size exceeds this 16 MB limit then it will only crawls the basic meta data which are associate to that list /library such as Title, Created By, Modified By. The contents inside the file will not be crawled.  

To increase this limit we can run the following PowerShell script. But the impact will be on the crawl time. So we have to analyze the environment and perform this action.

$ssa = Get-SPEnterpriseSearchServiceApplication

$ssa.SetProperty(“MaxDownloadSize”, 25)


We can set this limit to specific file type as shown below:  

$ssa.SetProperty(“MaxDownloadSizeExcel”, 25) 


Manage SharePoint incremental crawl index deletion


In SharePoint 2013, we have a content source that connects via BCS. The total items in the External table are around 7.7 million records. We have a full crawl scheduled to crawl the BCS items every month and an incremental crawl to run every 4 hrs.

In our environment the full crawl takes 18 hrs to crawl 7.7 million records and incremental crawl takes 2-3 hrs based on the number of items modified in the external table.

Things were moving fine until the incremental crawl deleted all the indexes from the content source when it failed (due to permission issue) to crawl BCS items. When we checked the crawl log we found that the incremental crawl only triggered the delete operation in the SharePoint Search Service.

Finally we came to know that Microsoft enforced some policies in SharePoint Search Service to delete the index based on some conditions. I am not sure we can disable this setting but we can increase the thresholds through PowerShell.

If somebody knows how to disable this setting / policy please let me know….

Solution / Workaround

Please find the below article which explains about this issue:


But few intervals are changed in 2013 version of SharePoint

Comparison of Default Values between 2010 and 2013:

Thanks to Simon’s blog for sharing this information in his blog.  

Property SP 2010 SP 2013
ErrorDeleteCountAllowed 30 times 10 times
ErrorDeleteIntervalAllowed 720 hours 240 hours
ErrorCountAllowed 100 times 15 times
ErrorIntervalAllowed 1440 hours 360 hours
RecrawlErrorCount 10 times 5 times
RecrawlErrorInterval 360 120 hours
DeleteUnvisitedMethod 1 1



SharePoint 2013 Search with Refiner Webpart Out of memory error Issue

I have been waiting for an answer for the following issue. I felt it’s a MS bug since I can reproduce this in the OOB SharePoint 2013 page without any customization. Before I conclude this as bug I wanted to confirm with Microsoft.

Finally I came to know from a Microsoft field engineer that it’s a MS bug which they are going  to fix in the April 2014 CU.

To Reproduce please follow the below steps:

  • Create new site collection
  • Create new a Page
  • Add search results &  search refiner WebParts in the new page created
  • Search for something
  • Click on any refiner column and filter the result
  • Click browser back button. You will find the following message.


FYI: The same page will work without any error if you remove the refinement panel WebPart from the page.

So let us wait for the April 2014 CU.

SharePoint Search Suggestions

Where the Search Suggestions are saved in SharePoint?

When we start typing the keywords in the SharePoint search box control it internally performs an AJAX call to
/_vti_bin/client.svc/ProcessQuery and that calls the stored procedure proc_MSS_GetQuerySuggestions.
The proc_MSS_GetQuerySuggestions stored procedure gets the suggestions from the MSSQLogSearchCounts table.
This table exist in the <Search Service Application Name>__DB_<GUID> Database.

How Suggestions are saved?

  1. Manual ( using code / PowerShell / csv file import from central admin)
  2. Automatically by prepare query suggestions timer job.

What determines a query suggestion?

  • Query suggestions are based on frequently used search terms and click through to results. Listed below are the steps needed for SharePoint to promote a term to a query suggestion.
  • The associated Search Service application must have query logging enabled.
  • The user must type in a valid word or set of words in the search box.
  • The user must click on 6 or more search result links and open or save the file. Some types of pages containing the term can qualify as click-throughs.
  • The Query Logging timer job must be run.
  • The Prepare Query Suggestions time job must be run.

Some Powershell Operations related to Search Suggestion

#Add Single Suggestion

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue
$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner
New-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $searchapp -Language En-Us -Type QuerySuggestionAlwaysSuggest -Name
“Honda” -Owner $owner
Start-SPTimerJob -Identity “prepare query suggestions”

#Add More than one Suggestion

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue
$SearchSuggestionsList = @(“Car”, “Honda”, “F1”)
$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner

foreach ($Suggestion in $SearchSuggestionsList)
New-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $searchapp -Language En-Us -Type QuerySuggestionAlwaysSuggest –
Name $Suggestion -Owner $owner

Start-SPTimerJob -Identity “prepare query suggestions”

#Get All Search Suggestions

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue

$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner

#Remove Single Search Suggestion

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue

$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner

Remove-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $searchapp -Language En-Us -Type QuerySuggestionAlwaysSuggest –
Identity “Honda” -Owner $owner

Start-SPTimerJob -Identity “prepare query suggestions”

Suggestions Table

Configure item level security in SharePoint BCS

In SharePoint 2007 version we can only apply the query time security trimming on BDC Entities, Where in SP 2010 / 2013 you can implement the security trimming in the Crawl time also.

If you wish to trim your SharePoint search results on external system using BCS based on your own logic, there are two approaches:

  • CRAWL time security trimming
  • QUERY time security trimming

Applying security trimming logic at query level needs to be checked against every item returned from the search query for current user. It’s a very heavy process. When we use NTLM users to apply permission for the individual items in the external system then it’s better to go with CRAWL time security trimming.  In the crawl time security trimming approach, you just need an additional column in the external system table which will be used to save the permission details for each item. This will work if you are building a new BCS Model from the scratch. It’s very difficult to apply crawl time security trimming logic in an ongoing complex BCS application. So in that case you can better use query time security trimming approach.

Please refer this article – http://msdn.microsoft.com/en-us/library/gg294169.aspx

Searching SharePoint Results from Windows Explorer using OSDX and Custom RSS File


SharePoint 2013 Enterprise Search with Windows 7 Operating Systems


To understand what is a OSDX file and how to add an OSDX file as a search provider in Windows Explorer please refer the below links:



When I use the OOB SharePoint RSS file to show the results in the windows explorer I was not able to map the custom managed properties to the windows attributes such as Summary,  thumbnail, etc.,

The OOB SharePoint RSS file located in the following location:


Regarding this issue, when I approached some MVPs and Microsoft Windows Development team they replied that it is not possible to map the SharePoint managed properties with the windows explorer search result attributes.

Some of the articles are also explaining the same



So what is the solution for this?


Windows 7 is the first OS with the capability to use external search providers as federated search locations – and yes, this federated location can also be SharePoint!  In general, all search providers can be federated if it’s OpenSearch 1.0/1.1 compatible or, in other words, can provide the search results in RSS/Atom format.

So that means if I have to create my own RSS feeding file then my custom code should return the SharePoint search results in RSS/Atom format. So I started exploring what is the best way to get the search results. Then I found a interesting feature in SharePoint 2013 called “SharePoint 2013 Search REST API”. This new REST service is the best way to go in a variety of application scenarios. External Applications to SharePoint that require search functionality can also leverage this service as the endpoint for communication into the Search platform. The old search.asmx SOAP web service is marked as deprecated in SP2013, and the new Search REST service is the replacement.

Location of the Search Rest service

The Search REST service is located at the following URI: http://host/site/_api/search

See more about this from the below link


Using the above mentioned API I am supplying the keyword to the API and getting the XML output. From the XML output I am extracting the necessary XML Node values and populating the output in the format of RSS/Atom using the below code:

Front end Code

<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”SearchAppliedRSS.aspx.cs” Inherits=”SearchResultsRSS.Layouts.SearchResultsRSS.SearchAppliedRSS” ContentType=”text\xml” %>

<asp:Repeater ID=”ResultRSSRepeater” runat=”server”>


<rss version=”2.0″>



<link>Configure you site URL</link>


Nucleus Search Center Site.





<title><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Title”)) %></title>

<link>Item URL</link>

<description><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Description”))%></description>

<category><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Category”)) %></category>

<pubDate><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “LastModifiedTime”))%></pubDate>

<author><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Author”))%></author>








Back end Code

protected void Page_Load(object sender, EventArgs e)


string keyword = Request.QueryString[“keyword”];

string query = “http://<Site Name>/_api/search/query?querytext='” + keyword + “‘&sourceid=’c9a51276-5b58-423c-88cb-a64297974cb4’&selectproperties=’Author,TCItemType,TCItemName,Description,LastModifiedTime'”;

WebRequest req = WebRequest.Create(query);

NetworkCredential myCred = new NetworkCredential(“<Admin Account User ID>”,”Password”,”Domain Name”);

req.Credentials = myCred;

WebResponse resp = req.GetResponse();

System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());

// process response..

XDocument oDataXML = XDocument.Load(sr, LoadOptions.None);

XNamespace atom = “http://www.w3.org/2005/Atom”;

XNamespace d = “http://schemas.microsoft.com/ado/2007/08/dataservices”;

XNamespace m = “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”;

List<XElement> items = oDataXML.Descendants(d + “query”)

.Elements(d + “PrimaryQueryResult”)

.Elements(d + “RelevantResults”)

.Elements(d + “Table”)

.Elements(d + “Rows”)

.Elements(d + “element”)


// Extracting the values from XML..

DataTable dt = new DataTable();






foreach (XElement item in items)


DataRow dr = dt.NewRow();

//XElement xItem = item.Element(d + “Cells”).Descendants(d + “Key”).First();

foreach (XElement xItem in item.Element(d + “Cells”).Descendants(d + “Key”))


if (xItem.Value == “TCItemName”)


dr[“Title”] = xItem.Parent.Element(d + “Value”).Value;


else if (xItem.Value == “Author”)


dr[“Author”] = xItem.Parent.Element(d + “Value”).Value;


else if (xItem.Value == “Description”)


dr[“Description”] = xItem.Parent.Element(d + “Value”).Value;


else if (xItem.Value == “LastModifiedTime”)


dr[“LastModifiedTime”] = xItem.Parent.Element(d + “Value”).Value;


dr[“Category”] = “Teamcenter Item”;




// data-bind to ListView..

ResultRSSRepeater.DataSource = dt;



protected string RemoveIllegalCharacters(object input)


// cast the input to a string

string data = input.ToString();

// replace illegal characters in XML documents with their entity references

data = data.Replace(“&”, “&amp;”);

data = data.Replace(“\””, “&quot;”);

data = data.Replace(“‘”, “&apos;”);

data = data.Replace(“<“, “&lt;”);

data = data.Replace(“>”, “&gt;”);

return data;


Finally instead of configuring the SharePoint OOB search RSS file, I have configured the customized RSS Feed file which I have created using the above code in the OSDX file.

How to create RSS?


Now I am able to see the results with the format I have specified in the code. Please see the below screen:

Search Database Related Tables using BCS

The following solution is designed and developed with the help of my friend (Sreeharsha) and Scott Hilier


SharePoint 2013 Enterprise with all the Services enabled on the same server.

SQL Server 2008 R2 Standard also on the same SharePoint standalone server.


Configure BCS to search a Database, where the Tables have a parent-child relationship. When searched for any term within the child table, the item linked in the parent item has to be displayed on the result page than the searched term or item from the child table.

The requirement makes sense in a way, that the child tables are considered as paragraphs within a document. When searched for a term within the paragraph, you would expect the link to the document to be displayed. After all the child tables will always be additional information about the parent item.


While designing for this requirement the data to be retrieved from the parent table and the child table has to be from two different entities, however they need be within the same LOBInstance.

By which, we have control to define the association between these entities within the Model.

Here is how the Method and the method instance need to be defined.

<Method Name=”PartItemToRevisionItem”>


<Property Name=”HideOnProfilePage” Type=”System.Boolean”>true</Property>



<Parameter Name=”partID” Direction=”In”>

<TypeDescriptor Name=”PartID” TypeName=”System.String” IdentifierEntityName=”PartItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”PartID” ForeignIdentifierAssociationName=”PartItemToRevisionItem” />


<Parameter Name=”revisionAssociationItemList” Direction=”Return”>

<TypeDescriptor Name=”RevisionItemList” TypeName=”MultiBCS.MultiEntity.RevisionItem[], MultiEntity” IsCollection=”true”>


<TypeDescriptor Name=”RevisionItem” TypeName=”MultiBCS.MultiEntity.RevisionItem, MultiEntity”>


<TypeDescriptor Name=”RevisionID” TypeName=”System.String” IdentifierEntityName=”RevisionItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”RevisionID” />

<TypeDescriptor Name=”Revision_Name” TypeName=”System.String” />

<TypeDescriptor Name=”Revision_Cost” TypeName=”System.Int32″ />

<TypeDescriptor Name=”PartID” TypeName=”System.String” />

<TypeDescriptor Name=”Revision_Type” TypeName=”System.String” />








<Association Name=”PartItemToRevisionItem” Type=”AssociationNavigator” ReturnParameterName=”revisionAssociationItemList” IsCached=”false”>


<Property Name=”AttachmentAccessor” Type=”System.String”></Property>


<SourceEntity Name=”PartItem” Namespace=”MultiBCS.MultiEntity” />

<DestinationEntity Name=”RevisionItem” Namespace=”MultiBCS.MultiEntity” />




The association method can be written in any entity (parent or child). But the tag that defines the source and the destination can be seen within the MethodInstance.

Silver Bullet:

Of all this requirement the most important part and the one that defines that the child tables need to be crawled as attachments by which I mean that, “when searched for a term in the child table, the item linked in the parent table to be displayed”, the below Tag needs to be added within the method instance, otherwise which multiple results will be displayed for a single record which is unwanted.

<Property Name=”AttachmentAccessor” Type=”System.String”></Property>

Thanks to Scott Hilier to the right pointer on the “Attachment Accessor”  which is significant for this requirement.


How to: Crawl associated external content types in SharePoint 2013

Modeling Associations in External Data


Use of UseClientCachingForSearch property in BDC Model


  • SharePoint 2013 Enterprise with all the Services enabled on the same server.
  • SQL Server 2008 R2 Standard also on the same SharePoint standalone server. 


We are using a custom BCS connector to crawl the Data from SQL for SharePoint 2013 search. There are 2.1 million records in the SQL table. The data is being read in batches and provided to the crawler (we have implemented our own logic to supply the records to the search crawler in batches).

The issue here is the crawl rate.  The rate of crawl at which the OOB External Content type retrieves the data is way higher than the custom process (2.1 M items in 8.30 hrs.). But when we try to crawl the BCS External Content type using custom BCS connector its takes 10 hrs. (If the load is high or multiple users log into the server sometimes it takes 19 hrs. to crawl 1M items)

At this point of time we are unable to identify the cause of the issue or find the section where the bottle neck is.


When we compared the OOB and Custom BDC model files (.bdcm), we found that there is one additional property called UseClientCachingForSearch was used in the OOB BDC model. Then we started investigating about this property and found that this is the property produce the magical number for OOB BCS.

The UseClientCachingForSearch property in BDC model improves the speed of full crawls by caching the item during enumeration. This property is also recommended when implementing incremental crawls that are based on change logs, because it improves incremental crawl speed.

Basically when we make a model using Out of the box, the UseClientCachingForSearch property is added automatically. When we make our own custom BCS model we have to add this property manually.

Important Note:

If BCS crawl items are larger than 30 kilobytes on average, we should not set this property, as it will lead to a significant number of cache misses and negate performance gains. In our case our records are not more than this limit.


After implementing this we got the magical number same like out of the box BCS crawl in our custom BCS crawl activity. This is just in the development environment. When it goes to production it should give very good performance for 200% sure.

Why Custom BCS Connector?

Custom BCS connector is used here for the following reasons:

  • Out of the box BCS Connector has response limit. The maximum number of rows that can be read through Database Connector is 1,000,000. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet using PowerShell. But it is not recommended. To handle this threshold issue on the search crawler, we are writing the custom BCS connector with the batch processing. (Please refer the SharePoint 2013 Software Boundaries)
  • Currently our external system (Teamcenter) has 2.1 million data and same may grow up to 6 million in the next 4 to 5 years. To process this dynamic data growth we have implemented batch processing in our Custom BCS connector.
  • OOB SharePoint incremental crawl will not delete the deleted items in the external system from the search indexes until we do a full crawl. To implement this logic for incremental crawl we choose custom BCS connector.

Vote of Thanks

Regarding this solution approach, I would like to convey my thanks to Subrat Naik who helped us to create the core BCS custom connector. To improve the performance at the code level Sivasankar Sabbani Pillai and Prasad Athalye did a great code review. To improve the crawl and search performance Scot Hillier and Matthew McDermott helped us a lot. Scot & Matthew the MVPs helped us a lot to get this solution work with greater performance in tough testing scenarios.

Last but not least Sreeharsha Alagani the Search Consultant who is working with me in this project. He has a very good knowledge in SharePoint Search Applications.

In this project the researches, findings and learning we have done are going to be Great Knowledge Base for everyone us/you.

Feel free to get in touch with me if you have any queries on Custom BCS connector / SharePoint 2013 Search. I am happy to help you.

Thank you every one!!! Happy SharePointing !!!