ULS log Event ID 5240 �There was an error in communicating with Excel Calculation Services�

Issue Description

Recently I came across this issue where some of the end users were getting an error message when they tried to open an Excel file in SharePoint in the browser.

Unable to process the request.

Wait a few minutes and try performing this operation again.

- On checking the ULS logs I got the below error message.

ULS log Event ID 5240 �There was an error in communicating with Excel Calculation Services�

- On checking the Event Viewer on the server , there was the below error.

Event 2159, SharePoint Server

Event 5240 (Excel Services Application) of severity Error occurred 20 more time(s) and was suppressed in the event log

Cause

The Excel Services Application server computer is in a non-valid state and may need to be restarted.

Resolution

Most of the online articles were asking me to restart the Central Administration server.I did not want to do that and found a different fix.

Identify the application pool running the Excel Services service application (from IIS) and Recycle it.

1) Start �>Run �> inetmgr

2) Under the Sitesclick on SharePoint Web Services. This will list down the Services in SharePoint.

3) Click on each GUID �> Change the view from Features View to Content View

4) On finding ExcelService.asmx-> Right click that web service -> Manage Application -> Advanced Settings.

5) Identify the Application Pool (make a note of this).

6) Now in IIS�> Application Pools �> choose the Application Pool you noted down in previous step   -> Recycle (recycle the application pool).

SharePoint Shell Admin Access

SharePoint Shell Admin Access

To execute a command in the SharePoint 2010 Management Shell, the user should open the Management Shell on a SharePoint 2010 server. To execute commands, the user must have the necessary privileges to the SharePoint object model and to the SharePoint databases. When you are working with SharePoint objects, connections to the database are generally created with the permissions of the current user. 

In order to use Windows PowerShell for SharePoint , a user must be a member of the SharePoint_Shell_Access role on the configuration database and a member of the WSS_ADMIN_WPG local group on the computer where SharePoint is installed. 

You can use SPShellAdmin for configuring a user�s privileges and security. Here�s how. 

To run this cmdlet to add a user to the SharePoint_Shell_Access role, you must have membership in the securityadmin fixed server role on the SQL Server instance, membership in the db_owner fixed database role on all affected databases, and local administrative permission on the local computer.

1. To list Shell administrators
Get-SPShellAdmin 

2. To add a user as a Shell administrator
Add-SPShellAdmin -UserName <user name> -Database <database name> 

Note

- If you specify only the user, the user is added to the role for the farm configuration database.

- If you use the database parameter, the user is added to the role on the farm configuration database, the Central Administration content database, and the specified database. Using the database parameter is the preferred method because most of the administrative operations require access to the Central Administration content database.

$db = Get-SPContentDatabase -Site http://sharepointsiteurl/

Add-SPShellAdmin -UserName "domain\user name" -Database $db

3. To remove a user from Shell Admin
Remove-SPShellAdmin -UserName <user name> -Database <database name> 

Deleting a SharePoint list through PowerShell

When SharePoint lists are corrupted and you are unable to delete them from UI, you can delete them from SharePoint PowerShell. If you want to force delete a SharePoint list using PowerShell, open SharePoint Management shell and run the below commands:

$web = Get-SPWeb -Identity http://sitename

$list = $web.Lists["corrupted list name"]

$list.AllowDeletion = $true

$list.Update()

$list.Delete()

Error Message: Content Advisor Will Not Allow You to See This Site

Issue Description

When you attempt to access a Web page using Internet Explorer, Content Advisor may display the following error message:

Sorry! Content Advisor will not allow you to see this site.

The error message also indicates that the page has no rating.

Cause

This issue can occur when Content Advisor is enabled, but the "User can see sites which have no rating" option is disabled.

Resolution

This is not a SharePoint issue. This issue is an Internet Explorer Browser issue.

Please refer to this KB article for the fix

SharePoint 2010 .htm files prompts user to download the file

Issue Description

When the user click on a .htm file in SharePoint site , user will be prompted to save the file rather than the file opening in the browser.

Solution

1) Open Central Administration -> Manage web applications -> Select web application -> General Settings

2) Web Application General Settings -> Browser File Handling ->Strict. Change this to Permissive

Workaround

Rename your files with .aspx extension (*.aspx)

Moving IIS SMTP Directory to a different drive location

After installing the SMTP server for enabling SharePoint incoming email feature, I had noticed that the IIS SMTP directories are by default placed in the location: C:\inetpub\mailroot

I wanted to move this to E: drive. 

The general command to do this is :

cscript.exe [directory]/[file.vbs] set smtpsvc/1/[command] [dir destination]

Below are the steps to do this:

1. Browse to Internet Information Services (IIS) 6.0 Manager

2. Right-click Default SMTP Virtual Server and Stop

3. Create the new folder locations (Copy the current inetpub folder to a different drive/partition) or manually create the necessary folders

4. Open command prompt (Run as administrator)

cd C:\inetpub\AdminScripts

Cscript.exe adsutil.vbs set smtpsvc/1/dropdirectory E:\inetpub\mailroot\Drop

Cscript.exe adsutil.vbs set smtpsvc/1/badmaildirectory E:\inetpub\mailroot\Badmail

Cscript.exe adsutil.vbs set smtpsvc/1/pickupdirectory E:\inetpub\mailroot\Pickup

Cscript.exe adsutil.vbs set smtpsvc/1/queuedirectory E:\inetpub\mailroot\Queue

6. Browse to Internet Information Services (IIS) 6.0 Manager

7. Right-click Default SMTP Virtual Server and Start

SharePoint List View Lookup Threshold

With the release of SharePoint 2010, resource throttling was introduced at the web application level to limit the number of items a list view (database query) can process in a single request, as well as to limit the number of lookup columns a single view can utilize at one time.

By default, List View Lookup threshold is set to 8.

To view the settings page for

Central Admin -> Application Management -> Manage Web Applications -> Select the web application -> General Settings -> Resource Throttling

As per TechNet,

�Each lookup column in a list view causes a join with another table. Each additional lookup column in a view increases the complexity of metadata navigation and list view queries. In addition to standard lookup columns, single-value managed metadata, multiple-value managed metadata, single-value people and group columns, and multiple-value people and group columns count as lookup columns. Adding lookup columns to a view does not cause a gradual or linear decrease in performance, rather performance is somewhat stable until after eight columns when it rapidly degrades.�

In short, all the below column types are considered towards list view look up threshold:

- Standard lookup columns

- Single-value managed metadata columns

- Multiple-value managed metadata columns

- Single-value people and group columns

- Multiple-value people and group columns

Note

As the number of look up columns increases beyond the default value in a list, you will come across performance issues in your farm.