No results in SharePoint Search but it exists in index

SharePoint Search crawler succeeds crawling the content and the same is available in crawl log. There is no crawl log error, content is a plain MS Word document, Web Application is properly associated with Search Service application but unable to see not even a single search result.

This was bit embarrassing and to nail down this issue, I was trying look in to Windows Event logs and SharePoint ULS logs, but there is no interesting message there for me to troubleshoot.

Removed the SSA association and deleted the SSA. Created a new SSA and did the association again. But the result is "none"

Verified that the Search service account has read permission in the web application and in the database as well. They exists perfectly but i don't not even a single search result.

Finally enabled the "Verbose" mode ULS logging and tried to filter using "Query Processor" category. There was new things started appearing in verbose mode

AuthzInitializeContextFromSid failed with 1355. The querying user's Active Directory object may be corrupted, invalid or inaccessible. Query results which require non-Claims Windows authorization will not be returned to this querying user.

Not sure why this exception occurs under "Unexpected" category but the recently I've configured a local admin account as service account through Power shell scripts. Probably this might be the culprit.

After hours of re-search found an MS KB article, which explained the same symptoms and cause.

Open up your SharePoint 2010 Management Shell console as an administrator and run the following PS script.

$searchApp = Get-SPEnterpriseSearchServiceApplication "SSA Name"
$searchApp.SetProperty("ForceClaimACLs",1)

Do a full crawl on all content sources to get the search results. Don't forget to switch off the verbose mode ULS Logger.

Render a spreadsheet as HTML with Excel REST services

                             An interesting question from one of my peer, whether we can show excel spreadsheet using REST services and they don't want all the features of Excel Web access web part. Just show the spreadsheet on a web page.

                            This question drove me to take a look at the capabilities of Excel services. IMHO Excel services were made for calculation and resource-heavy number crunching jobs along with a Excel web access web part which is used to render the sheets. But after looking at the REST API(yes it's beautiful), it totally revamped my thinking about this service.

First things first

1. You need an SharePoint 2010 Enterprise Sever and Excel services should be configured properly in that
2. Make sure you see ExcelRest.aspx under \14\ISAPI.

How to use this ExcelRest.aspx ?

       Say you have an Excel file stored in a document library path like below

http://Foo/Site/Shared Documents/Employee.xlsx

then the URL will be

http://Foo/Site/_vti_bin/ExcelRest.aspx/Shared Documents/Employee.xlsx

           If you are like me and worked a lot with asmx web services and hitting just the endpoint http://Foo/Site/_vti_bin/ExcelResta.aspx you will get HTTP 400 error because it's simple, REST is representational and nothing is represented here, its just an endpoint.

How to show as HTML and what are the other options ?

1. /_vti_bin/ExcelRest.aspx/Shared Documents/Employee.xlsx/Model - Find more subsects(Ranges,Charts,Tables, PivotTables) of what you are going to query.
2. /_vti_bin/ExcelRest.aspx/Shared Documents/Employee .xlsx/Model/Ranges('Sheet1!A1|H15')?$format=html - From sheet 1, Fetch the data specified in the range and render in HTML.
3. /_vti_bin/ExcelRest.aspx/Shared Documents/Employee.xlsx/Model/Charts(‘RevenueChart’)?$format=image - Fetch the chart and render it as a PNG image

What are the formats available for me to render ?

1. ?$format=html  - Renders as plain HTML fragment, for tables,sheets and pivot tables
2. ?$format=atom  - provides you a ATOM feed
3. ?$format=image - Renders an PNG image, only supported for Chart outputs
4. ?$format=workbook - Downloads the whole workbook.

Although there are some unsupported features, given the abilities of OOTB Excel services luxuries like load balancing the requests,caching etc it is worthwhile in a scalable solution.

Want to learn more, refer MSDN

1. http://msdn.microsoft.com/en-us/library/ee556413
2. http://blogs.office.com/b/microsoft-excel/archive/2009/11/05/excel-services-in-sharepoint-2010-rest-api-syntax.aspx
3. http://blogs.office.com/b/microsoft-excel/archive/2009/11/09/excel-services-in-sharepoint-2010-rest-api-examples.aspx