Problem: One of our clients reported when he tried to open Excel workbook which contains Pivot table from the SharePoint Pivot Gallery, after doing the refresh, the Excel workbook doesn’t open or opens in the repair mode.

Background: Office 2010, Excel 2010. Windows 7 64 bit.

Troubleshooting:

  • Checked if multiple versions of office installed.
  • Downloaded the local copy of the file and it was opening fine.
  • Checked the content of the Excel file that does contain the pivot tables and using the external connection in the source file was fine.
  • Tried to use the different connection string in the Excel, i.e. fetching data from any other external source.
  • Local copy of the file was working fine, whereas when publishing to SharePoint it breaks.

Resolution: This was a prolonging issue and MS has confirmed that this is not a SharePoint issue and has to be the SQL 2008 issue, which got fixed in the Cumulative update 2 for SQL Server http://support.microsoft.com/kb/2072493 .

You can also get a hot fix which is supported by KB http://support.microsoft.com/kb/2028769

If you face any difficulties or have any further queries, please flag them, I would be happy to assist you with them.

Applies to:

  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SharePoint Server 2010
  • Microsoft Excel 2010