I worked a case today where they were trying to get SQL 2012 Reporting Services installed in a SharePoint environment that also had SQL 2008 R2 PowerPivot installed. This, by itself, is fine and wasn’t really causing the problem. When they tried to open the Excel Workbook from SharePoint that had the PowerPivot data in it, we saw the following error:
We found the following within the SharePoint ULS Log for the Excel Calculation Services category:
ExternalSource.ExecuteOperation: We exhausted all available connection information. Exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException' was thrown.
at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)
at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOperation, Boolean verifyPreOperationConnection), Data Connection Name: PowerPivot Data
What the error above is indicating is that it was trying to establish a connection from the defined data connections within the Excel Workbook. One of these connections is the PowerPivot Connection for the PowerPivot data stored within the workbook itself. This connection/Data source is called “PowerPivot Data”. When we looked at that connection, we found the following:
You can get to this information by going to the Data Tab within Excel and clicking on “Existing Connections”. Then Right click on “PowerPivot Data” and edit the connection properties. Then go to the Definition tab.
The key here is the MSOLAP.5 Provider. This provider is the PowerPivot 2012 Provider. However, within the SharePoint environment, we had the SQL 2008 R2 version of PowerPivot. These versions are not compatible. Also, the MSOLAP.5 provider does not exist because we hadn’t installed it. It comes with the PowerPivot 2012 install. So, the error above is really saying that it couldn’t find the MSOLAP.5 provider. Which in this case is true.
This is all about aligning the PowerPivot version of the workbook with the PowerPivot version of the Server. I think what may have caused this situation is that when you go to http://powerpivot.com and click on the “Download PowerPivot” button, it takes you to the SQL 2012 PowerPivot Add-in for Excel.
We have two options at this point:
- Upgrade the SharePoint environment to the 2012 version of PowerPivot
- Downgrade the Excel Add-in to the 2008 R2 version for PowerPivot and recreate the Excel Workbook.
SQL 2012 PowerPivot Add-in for Excel: http://www.microsoft.com/en-us/download/details.aspx?id=29074
SQL 2008 R2 PowerPivot Add-in for Excel: http://www.microsoft.com/en-us/download/details.aspx?id=7609
Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton