Hi there,

I am having some strange issue with my PowerPivot for SharePoint but can't figure out what is causing this. Here is what happened:

I setup PowerPivot (PP) for SharePoint and activate it on few site collections in production environment; I upload a PP workbooks in a library, manage the data refresh schedules, run the data refresh for an external SQL databases, etc. Everything works fine for several days.

One day we decide the move one of the site collections (X) from its current content database to its own (new) content db. The way we did it is that we took the site collection backup in production, restored it in test environment (in its own content db; we deleted the old site collection in test first before restoring), checked everything including PP data refreshes etc.; all worked fine.

Then we did the same thing in production - we deleted the old site collection X, restored the site collection from the same backup file that we used in test environment, everything works fine EXCEPT the PowerPivot refreshes!! :-(

I am getting these errors: When I click on the workbook, it gives this error "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh: PowerPivot Data"

When I click OK on error dialogbox, it opens the workbook fine but no slicers/refreshes work now. When I go back to the library and click on "Manage PowerPivot Data Refresh" to open the refresh history/schedule page, it gives this generic but scary error: "An unexpected error has occurred. Troubleshoot issues with Microsoft SharePoint Foundation." ULSViewer or Event viewer is not showing anything related to this error!

Strange thing is that PP refresh works fine in Test environment, as well as on other site collections in production that we didn't touch (which tells me there is nothing wrong with my PP configuration). Did the backup/restore in prd cause anything? Did moving it in its own contentdb cause anything? (But then why it works in test environment?) Is there anything wrong with site collection PP feature? Can force reactivating it help??????? I am just lost and going crazy now!

Please help.

Asif

I have found some entries in ULS logs seemingly relevant to this issue as below: Please help. Thanks.

Note: I have replaced urls with <url> or <server> and username with "domain\user".

ExternalSource.GetExternalKeyAndStateAndMarkUsed: Populating the session's credentials for external data. Index=0, Credentials=[14337250, domain\user]

ConnectionRequest.ConnectionRequest: New connection request. SessionId=1.V24.1916ClMR0rOQ1F8mQPqtil0V90.5.en-US5.en-US73.-0600#0000-04-00-01T03:00:00:0000#+0000#0000-10-00-01T02:00:00:0000#-006036.7dcb49ef-0dc4-45d2-a0f8-4dc0e18a70f41.N, WorkbookVersion=ConnectionInfo.WorkbookVersion: Uri=http://<url>/SQL_PowerPivotTest.xlsx, Version=Monday, 09 September 2013 06:29:42

Check whether "http://<url>/SQL_PowerPivotTest.xlsx" is in the farm.

Connecting to "http://<server>/_vti_bin/PowerPivot/Redirector.svc/?DataSource=/team/IT/SiteCollectionDocuments/SQL_PowerPivotTest.xlsx&LocaleID=1033".

WcfSendRequest: RemoteAddress: 'http://<server>:32843/0a4610a7f75f4ff582ca7329256f0fe3/SSASMidTierService.svc' Channel: 'Microsoft.AnalysisServices.SharePoint.Integration.IGeminiServiceApplicationClient' Action: 'http://tempuri.org/IGeminiServiceApplication/GetDatabase' MessageId: 'urn:uuid:44f67e36-7ec2-4fe8-bd4c-277c9d9ddde7'

WcfReceiveRequest: LocalAddress: 'http://<server>:32843/0a4610a7f75f4ff582ca7329256f0fe3/SSASMidTierService.svc' Channel: 'System.ServiceModel.Channels.ServiceChannel' Action: 'http://tempuri.org/IGeminiServiceApplication/GetDatabase' MessageId: 'urn:uuid:44f67e36-7ec2-4fe8-bd4c-277c9d9ddde7'

Entering monitored scope (ExecuteWcfServerOperation)

Obtaining routing information for the request using <SERVER> - User:domain\user, DataSource: http://<url>/SQL_PowerPivotTest.xlsx, Version: 09/09/2013 06:29:42. (RequestStartTime=[<SERVER>])

Leaving Monitored Scope (ExecuteWcfServerOperation). Execution Time=10.7664521608193

EXCEPTION: System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Unable to find the specified file. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.IO.FileNotFoundException: Unable to find the specified file.  

 at Microsoft.SharePoint.SPFile.EnsureUniqueId()   

 at Microsoft.SharePoint.SPFile.get_UniqueId()   

 at Microsoft.AnalysisServices.SharePoint.Integration.Redirector.BackendRedirectService.GetDatabase(String loginName, String dataSource, String versionLabel, DateTime fileLastModifiedTimestamp, Int32 localeId, Boolean collectHeathInfo, Boolean createLocal, String serverEndpointAddress)   

 at Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplication.GetDatabase(String loginName, String dataSource, String dataSourceVersion, DateTime fileLastModifiedTimestamp, Int32 localeId, Boolean collectHeathInfo, Boolean createLocal, String serverEndpointAddress)   

 at SyncInvokeGetDatabase(Object , Object[] , Object[] )   

 at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)   

 at Sy...).

Proxy encountered an error while trying to get the sandbox information. This request will be terminated

EXCEPTION: System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Unable to find the specified file. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.IO.FileNotFoundException: Unable to find the specified file.  

 at Microsoft.SharePoint.SPFile.EnsureUniqueId()   

 at Microsoft.SharePoint.SPFile.get_UniqueId()   

 at Microsoft.AnalysisServices.SharePoint.Integration.Redirector.BackendRedirectService.GetDatabase(String loginName, String dataSource, String versionLabel, DateTime fileLastModifiedTimestamp, Int32 localeId, Boolean collectHeathInfo, Boolean createLocal, String serverEndpointAddress)   

 at Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplication.GetDatabase(String loginName, String dataSource, String dataSourceVersion, DateTime fileLastModifiedTimestamp, Int32 localeId, Boolean collectHeathInfo, Boolean createLocal, String serverEndpointAddress)   

 at SyncInvokeGetDatabase(Object , Object[] , Object[] )   

 at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)   

 at Sy...).

PF_CHECK_ERROR returned 'critical hresult error' 0x80004005 ; Stack Trace:NA

PF_CHECK_ERROR returned 'critical hresult error' 0x80004005 ; Stack Trace:NA

PF_CHECK_ERROR returned 'hresult error' 0x80040e41 ; Stack Trace:NA

ConnectionManager.GetConnection: Failed to create new connection, exception=Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown.   

 at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass3.<CreateConnection>b__0()   

 at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)   

 at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(ExecuteImpersonatedMethod method, Boolean dispose)   

 at Microsoft.Office.Excel.Server.CalculationServer.CredentialsDelegation.TryExecuteImpersonated(ExecuteImpersonatedMethod method)   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnection(Credentials credentials, ConnectionInfo connectionInfo, Int32 keyLcid, Uri workbookUrl, Boolean auditConnection, SessionId sessionId), sessionId=1.V24.1916ClMR0rOQ1F8mQPqtil0V90.5.en-US5.en-US73.-0600#0000-04-00-01T03:00:00:0000#+0000#0000-10-00-01T02:00:00:0000#-006036.7dcb49ef-0dc4-45d2-a0f8-4dc0e18a70f41.N, connectionString=Provider=MSOLAP.4;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue pool count=0

ConnectionManager.GetConnection: Caught an exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown.   

 at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass3.<CreateConnection>b__0()   

 at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)   

 at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(ExecuteImpersonatedMethod method, Boolean dispose)   

 at Microsoft.Office.Excel.Server.CalculationServer.CredentialsDelegation.TryExecuteImpersonated(ExecuteImpersonatedMethod method)   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnection(Credentials credentials, ConnectionInfo connectionInfo, Int32 keyLcid, Uri workbookUrl, Boolean auditConnection, SessionId sessionId)   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnectionAndAddToList(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Boolean auditConnection, Int32 keyLcid, ConnectionInfo connectionInfo, ConnectionKey connectionKey, AutoReaderWriterLock autoPoolLock, Connection& connection, ConnectionList& connectionList)   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection)

Refresh failed for 'PowerPivot Data' in the workbook 'http://<url>/SQL_PowerPivotTest.xlsx'. [Session: 1.V24.1916ClMR0rOQ1F8mQPqtil0V90.5.en-US5.en-US73.-0600#0000-04-00-01T03:00:00:0000#+0000#0000-10-00-01T02:00:00:0000#-006036.7dcb49ef-0dc4-45d2-a0f8-4dc0e18a70f41.N User: 0#.w|domain\user]

ExternalSource.ValidateConnection: Unable to get a connection: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown.   

 at Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInterop.InitConnection()   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.<>c__DisplayClass3.<CreateConnection>b__0()   

 at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(WindowsIdentity wi, ExecuteImpersonatedMethod method, Boolean dispose)   

 at Microsoft.Office.Excel.Server.Credentials.TryExecuteImpersonated(ExecuteImpersonatedMethod method, Boolean dispose)   

 at Microsoft.Office.Excel.Server.CalculationServer.CredentialsDelegation.TryExecuteImpersonated(ExecuteImpersonatedMethod method)   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnection(Credentials credentials, ConnectionInfo connectionInfo, Int32 keyLcid, Uri workbookUrl, Boolean auditConnection, SessionId sessionId)   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.CreateConnectionAndAddToList(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Boolean auditConnection, Int32 keyLcid, ConnectionInfo connectionInfo, ConnectionKey connectionKey, AutoReaderWriterLock autoPoolLock, Connection& connection, ConnectionList& connectionList)   

 at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection)   

 at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, ExtendedConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=1.V24.1916ClMR0rOQ1F8mQPqtil0V90.5.en-US5.en-US73.-0600#0000-04-00-01T03:00:00:0000#+0000#0000-10-00-01T02:00:00:0000#-006036.7dcb49ef-0dc4-45d2-a0f8-4dc0e18a70f41.N, externalSource=PowerPivot Data

ConnectionInfoManager.InitConnInfoFromOdcFile: ODC File Uri is not valid:

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, SessionId: 1.V24.1916ClMR0rOQ1F8mQPqtil0V90.5.en-US5.en-US73.-0600#0000-04-00-01T03:00:00:0000#+0000#0000-10-00-01T02:00:00:0000#-006036.7dcb49ef-0dc4-45d2-a0f8-4dc0e18a70f41.N, UserId: 0#.w|domain\user

Ok, here is the exception that I am getting for the unexpected error mentioned at the top. Note that the document does exist in the library and I can download it or open it in the browser. Also, proper permissions are granted.

System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]: Access is denied. The document you requested does not exist or you do not have permission to open the file.   Server stack trace:    

at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)   

at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)   

at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)   

at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)   

at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown

at [0]:    

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)   

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)   

at Microsoft.AnalysisServices.SharePoint.Integration.IGeminiServiceApplication.GetItemDataRefreshHistory(String url)   

at Microsoft.AnalysisServices.SharePoint.Integration.GeminiServiceApplicationProxy.GetItemDataRefreshHistory(String url)   

at Microsoft.AnalysisServices.SharePoint.Integration.ViewDataRefreshHistory.Page_Load(Object sender, EventArgs e)   

at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)   

at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)   

at System.Web.UI.Control.OnLoad(EventArgs e)   

at Microsoft.AnalysisServices.SharePoint.Integration.GeminiPageBase.OnLoad(EventArgs e)   

at System.Web.UI.Control.LoadRecursive()   

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

I have searched on the Internet, and have found that sometimes restoring a site collection in a different content database may cause some cache issues. I tried to flush the site collection object cache as well as BLOB cache but am not sure if that really worked because the problem is still there.

This is an issue in Production so any help will be extremely appreciated.

I resolved this issue today by running an IISRESET command on the WFEs. Actually it was site collection object cache that was causing this problem and IISRESET helped clearing the cache.

When one uses Move-SPSite cmdlet to move a site collection from one content database to another in the same farm, the IISRESET is required officially in the documentation. But, I used backup/restore site collection to move it from one content database to another in the same farm but did not do IISRESET assuming that backup/restore does not need it. But I am sure it would be required in this case. If the site collection is being backed up in one farm and then restore in another farm, then IISRESET is not required. I have checked this too.

Hope this is useful to someone.

• Marked as answer by Asif Khawaja Sunday, September 15, 2013 11:46 PM

I suspected a IISRESET would fix this and indeed it did, I didn't restore any site collections, we did a SP1 upgrade on SharePoint 2013, seems to have affected the PowerPivot refresh. Thanks for sharing Asif.

For anyone else this was the error in the ULS logs (some info removed for security):

High     EXCEPTION: System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Access is denied. The document you requested does not exist or you do not have permission to open the file. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: Microsoft.AnalysisServices.SPAddin.WBDS.RedirectorException: Access is denied. The document you requested does not exist or you do not have permission to open the file. ----> System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {0000} failed due to the following error: 800703fa Illegal operation attempted on a registry key that has been marked for deletion. (Exception from HRESULT: 0x800703FA).    at Mic...

• Edited by Dave Essential Enterprise Solutions Ltd Wednesday, August 13, 2014 7:02 AM Added error.

Well I am getting the same error after SP1 installation but IISreset still does not resolve the isse. Is there any thing else that needs or can be done?