SharePoint 2010 : The Workbook can not be opened

Scenario 1: 

Error: While opening the excel file in browser, it says "Non of supported extension can be opened"

ULS Log : SharePointFileLoader.GetSPFile: Sharepoint threw a handled exception - turning it into a FileOpen exception. Exception is: Microsoft.SharePoint.Upgrade.SPUpgradeCompatibilityException: There is a compatibility range mismatch between the Web server and database "TEST_SharePointDB", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to the same version and build level to return to compatibility range.     at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.ValidateBackwardsCompatibility()     at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)     at Microsoft.SharePoint.SPSite.GetSPRequest()     at Microsoft.SharePoint.SPSite.get_Request()     at Microsoft.SharePoint.SPSite.SetAllowUnsafeUpdates(Boolean allowUnsafeUpdates)     at Microsoft.Office.Excel.Server.MossHost.SharePointHelperMethods.GetSite(String fileLocation, IClaimsIdentity claimsIdentity)     at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity)

Scenario 2:

Error: While opening the excel file in browser, it says "The Workbook can not be opened"

ULS Log : ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=GenericFileOpenError; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: The workbook cannot be opened. ---> Microsoft.Office.Excel.Server.Host.HostFileException ---> Microsoft.SharePoint.Upgrade.SPUpgradeCompatibilityException: There is a compatibility range mismatch between the Web server and database "TEST_SharePointDB", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to the same version and build level to return to compatibility range.    

 at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.ValidateBackwardsCompatibility()    

 at Microsoft.SharePoint.SPSite.PreinitializeServer(SPRequest request)    

 at Microsoft.SharePoint.SPSite.GetSPRequest()    

 at Microsoft.SharePoint.SPSite.get_Request()    

 at Microsoft.SharePoint.SPSite.SetAllowUnsafeUpdates(Boolean allowUnsafeUpdates)    

 at Microsoft.Office.Excel.Server.MossHost.SharePointHelperMethods.GetSite(String fileLocation, IClaimsIdentity claimsIdentity)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity)     -

 -- End of inner exception stack trace ---    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.GetSPFile(IClaimsIdentity claimsIdentity)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.CheckForPermissions(IClaimsIdentity claimsIdentity)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.<>c__DisplayClass4.<TryExecuteWithUserContext>b__2()    

 at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.WithEnsureClaimsIdentitySetOnThread(IClaimsIdentity claimsIdentity, MethodToRun action)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostHelperMethods.TryExecuteWithUserContext(IIdentity userIdentity, Action`1 method)    

 at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.Init(Uri uri, Guid requestSiteId, IIdentity currentIdentity, IExcelServerDocumentContext documentContext, FileLoaderHostInfo& outFileLoaderHostInfo)    

 at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext)     -

 -- End of inner exception stack trace ---    

 at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.Init(Uri uri, Guid requestSiteId, IExcelServerDocumentContext documentContext)    

 at Microsoft.Office.Excel.Server.CalculationServer.FileLoader.CreateFromTrustedLocationAndInit(Uri uri, TrustedLocation trustedLocationSettings, Guid requestSiteId, IExcelServerDocumentContext documentContext)    

 at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.GetBaseWorkbookAndMarkUsedAsync(AsyncHandler`1 callback, Object userState, Request request, Uri uri, Boolean newWorkbook, Boolean useCollection, Boolean loadedOnDemand)    

 at Microsoft.Office.Excel.Server.CalculationServer.Session.OpenWorkbookAsync(AsyncHandler`1 callback, Object userState, Request request, Uri url, Boolean loadedOnDemand)    

 at Microsoft.Office.Excel.Server.CalculationServer.Operations.OpenWorkbookOperation.StartExecution()    

 at Microsoft.Office.Excel.Server.CalculationServer.Operations.Operation.RunOperationAsync()    

 at Microsoft.Office.Excel.Server.CalculationServer.Operations.OperationSite.PrepareComplete(PrepareAsyncArgs args)

Resolution Step 1 : Service application pool account should have DB Owner permission to site's(/sites/testapp/) content Database.

Resolution Step 2 : Recycle the Excel Calculation server application pool(must be a GUID) in all the servers where the Excel calculation service is started(find from Central Admin).

Start all the stopped websites in SharePoint 2010 farm (Using PowerShell)

# Start all the stopped websites in SharePoint 2010 farm
function StartStoppedWebSitesInFarm
{
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $websites = Get-WmiObject -namespace "root/webadministration" -class Site -ComputerName $server.name -Authentication 6 | Where {$_.GetState().ReturnValue -ne 1} | select name
     
        if ($websites -eq $null) {
            Write-Host "No web sites are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        {
            foreach ($webObj in $websites)
             {
                $web = $webObj.name
             
                if($web -eq 'Default Web Site')
                {
                   Write-Host "Default Web Site is stopped in $($server.Name), so skipping this server" -foregroundcolor yellow    
                   break  
                }
                else
                {        
                    Get-WmiObject -Namespace 'root\webadministration' -Class Site -ComputerName $server.name -Authentication 6 -Filter "Name='$web'" | Invoke-WmiMethod -Name Start
                    Write-Host "web site $web is started on server $($server.Name)" -foregroundcolor green
                }
             }
        }
     
    }
}

example:

Get all the web sites which are stopped in the SharePoint 2010 Farm (Using PowerShell)

# Get all the websites stopped in the SharePoint 2010 farm
function GetStoppedWebSitesInFarm
{
   
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $websites = Get-WmiObject -namespace "root/webadministration" -class Site -ComputerName $server.name -Authentication 6 | Where {$_.GetState().ReturnValue -ne 1} | select name
        if ($websites -eq $null) {
            Write-Host "No web sites are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        { 
            foreach ($web in $websites)
             {
                Write-Host "web site '$($web)' is stopped on server $($server.Name)" -foregroundcolor red
             }
        }
      
    }  
}
Example:

Start all the application pools which are stopped in the SharePoint 2010 Farm (Using PowerShell)

# Start the application pools stopped in the SharePoint 2010 farm
function StartStoppedAppPools
{
   
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $apppool = Get-WmiObject -Namespace root\MicrosoftIISv2 -Class IIsApplicationPoolSetting -ComputerName $server.name -Authentication 6 | Where { $_.AppPoolState -eq '4'} | select name
        if ($apppool -eq $null) {
            Write-Host "No app pools are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        { 
         foreach ($appObj in $apppool)
             {
        $app = $appObj | Out-String
           $apppoolname = $app.Substring($app.LastIndexOf('/') + 1,($app.Length-$app.LastIndexOf('/'))-1).Trim()
             
              if($apppoolname -eq 'SharePoint Web Services Root')
              {
                 Write-Host "App Pool $apppoolname does not require to start in $($server.Name)" -foregroundcolor yellow           
              }
              else{
            Write-Host "Starting the app pool '$($apppoolname)'"
            Get-WmiObject -Namespace 'root\webadministration' -Class ApplicationPool -ComputerName $server.name -Authentication 6 -Filter "Name='$apppoolname'" | Invoke-WmiMethod -Name Start
             
                  Write-Host "App Pool $apppoolname is started on server $($server.Name)" -foregroundcolor green
                 }
             }
        }
    }  
}

Example

Get all the Application Pools which are stopped in the SharePoint 2010 Farm (Using PowerShell)

# Get all the application pools stopped in the SharePoint 2010 farm
function GetAppPoolStatusFarm
{
   
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $apppool = Get-WmiObject -Namespace root\MicrosoftIISv2 -Class IIsApplicationPoolSetting -ComputerName $server.name -Authentication 6 | Where { $_.AppPoolState -eq '4'} | select name
       
        if ($apppool -eq $null) {
            Write-Host "No app pools are stopped on server $($server.Name)" -foregroundcolor green
            continue
        }
        else
        { 
            foreach ($appObj in $apppool)
             {
                  $app = $appObj | Out-String
               $apppoolname = $app.Substring($app.LastIndexOf('/') + 1,($app.Length-$app.LastIndexOf('/'))-1).Trim()
                 
                  if($apppoolname -eq 'SharePoint Web Services Root')
                  {
                      Write-Host "App Pool '$($apppoolname)' is stopped on server $($server.Name)" -foregroundcolor yellow
                  }
                  else
                  {
                    Write-Host "App Pool '$($apppoolname)' is stopped on server $($server.Name)" -foregroundcolor red
                  }
             }
        }
      
    }  
}

Example

Restart SharePoint Services on SharePoint 2010 farm

Step 1. Create a folder with 2 powershell blank files as below image.

Step 2. Open Sam.Functions.PS1 file and copy the below code snippet.

 # Stop specified service on all servers within the farm
function Stop-ServiceOnFarm
{
    param ($ServiceName)
   
    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
    {
        $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"
       
        if ($service -eq $null -or $service.State -eq "Stopped") {
            continue
        }
       
        Write-Host "Stopping '$($ServiceName)' on $($server.Name)..."
        $service.InvokeMethod('StopService',$Null)
       
        do {
            Start-Sleep -s 5
            $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"   
        } while ($service.State -ne "Stopped")
    }  
}

# Starts specified service on all servers within the farm
function Start-ServiceOnFarm
{
    param ($ServiceName)
   
     foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )
     {
        $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"
       
        if ($service -eq $null -or $service.State -eq "Started" -or $service.State -eq "Starting") {
            continue
        }
       
        Write-Host "Starting '$($ServiceName)' on $($server.Name)..."
        $service.InvokeMethod('StartService',$Null)
     }
}

# Restarts specified service on all servers within the farm
function Restart-ServiceOnFarm
{
    param ($ServiceName)
   
    Stop-ServiceOnFarm $ServiceName
    Start-ServiceOnFarm $ServiceName
}

# Restarts SharePoint Services on all servers within the farm
function Restart-TimerandAdminService
{
    Restart-ServiceOnFarm "SPTimerV4"
    Restart-ServiceOnFarm "SPAdminV4"

}

Step 3. Open the file Sam.RestartAdmin-TimerServicesOnFarm and copy the below snippet. 
if ($SCRIPT:scriptDir -eq $null -or $SCRIPT:scriptDir -eq "")
{
 $SCRIPT:scriptDir = (Get-Location -PSProvider FileSystem).ProviderPath
}

# ** Load My functions
Write-Host "* Loading Functions..."
. (Join-Path $scriptDir "Sam.Functions.ps1")

Restart-TimerandAdminService

Step 4. Close and Save the files and execute the .\Sam.RestartAdmin-TimerServicesOnFarm.ps1 file from any server in the farm.

Restart IIS in all servers in a SharePoint 2010 farm

Step 1. Create a folder with 2 powershell blank files as below image.

 

Step 2. Open Sam.Functions.PS1 file and copy the below code snippet.

# Stop specified service on all servers within the farm

function Stop-ServiceOnFarm

{

    param ($ServiceName)

     

    foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )

    {

        $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"

         

        if ($service -eq $null -or $service.State -eq "Stopped") {

            continue

        }

         

        Write-Host "Stopping '$($ServiceName)' on $($server.Name)..."

        $service.InvokeMethod('StopService',$Null)

         

        do {

            Start-Sleep -s 5

            $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"   

        } while ($service.State -ne "Stopped")

    }  

}

# Starts specified service on all servers within the farm

function Start-ServiceOnFarm

{

    param ($ServiceName)

     

     foreach ($server in (Get-SPServer | Where {$_.Role -ne "Invalid"}) )

     {

        $service = Get-WmiObject -computer $server.Name Win32_Service -Filter "Name='$($ServiceName)'"

         

        if ($service -eq $null -or $service.State -eq "Started" -or $service.State -eq "Starting") {

            continue

        }

         

        Write-Host "Starting '$($ServiceName)' on $($server.Name)..."

        $service.InvokeMethod('StartService',$Null)

     }

}

# Restarts IIS on all servers within the farm

function Restart-IIS

{

    Stop-ServiceOnFarm "W3SVC"

    Stop-ServiceOnFarm "SMTPSVC"

    Stop-ServiceOnFarm "IISADMIN"

     

   Start-ServiceOnFarm "IISADMIN"

   Start-ServiceOnFarm "SMTPSVC"

   Start-ServiceOnFarm "W3SVC"

}

 

Step 3. Open the file Sam.RestartIISOnFarm and copy the below snippet.

 

  if ($SCRIPT:scriptDir -eq $null -or $SCRIPT:scriptDir -eq "")

{

 $SCRIPT:scriptDir = (Get-Location -PSProvider FileSystem).ProviderPath

}

# ** Load My functions

Write-Host "* Loading Functions..."

. (Join-Path $scriptDir "Sam.Functions.ps1")

Restart-IIS

Step 4. Close and Save the files and call the .\Sam.RestartIISOnFarm.ps1 file from any server in the farm.