When you configure the data source of your Office 2010 Excel Power Pivot sheet, you can get the message:
Failed to connect to the server. Reason: The sqlncli10 provider is not registered on the local machine.
This can happen if you want to connect to a SQL 2008 (R2) server. On the client you need to have installed the Microsoft SQL Server Native Client (SQL Server Native Client). This is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver.
Solution: Install the sqlncli10 provider on your machine.
The sqlncli10 provider for SQL 2008 R2 can be downloaded from here.
In case you have a SQL 2012 server and have received a Power Pivot sheet which want to use the SQLNCLI10 provider, you can change the data provider to use to data provider for SQL 2012: SQLNCLI11
- Press the Existing connection button in the Power Pivot Window
- Select the PowerPivot Data Connection
- Press Edit
- Specify the SQL server name
- Specify the Database name
- Press the advanced
- Select Provider: SQL Server Native Client 11.0
After installation it should be possible to configure your datasource, to be able to update your Excel Power Pivot sheet with all data.