The daily Excel data update worked fine until… one day it didn’t. Obviously it’s just a few days before a presentation to our head of department 🙂
My colleague Tomer Horev, who’s in charge of the SharePoint end, was trying to find a reason for the connection failure. The error message was:
The refresh operation failed because the source data base or the table does not exist, or because you do not have access to the source
OLE DB or ODBC error: Unexpected error from external database driver ().; 3275.
A connection could not be made to the data source with the DataSourceID of ‘f3b6242a-ff67-4e84-8f20-847861d6e7ab’, Name of ‘Excel XYZ’.
An error occurred while processing the ‘DATA’ table.
The current operation was cancelled because another operation in the transaction failed.
Out of line object ‘DataSourceView’, referring to ID(s) ‘Temp_DSV’, has been specified but has not been used.
A link for Unexpected error from external database driver helped him understand that he needed to add Imex=1 in the connection string.
Just look under the Design tab for Existing Connections > choose the relevant connection > click the Edit button and then the Advanced button:
So, a failed data refresh for an Excel 2010 on SharePoint 2010 can sometimes be a broken PowerPivot connection.