Refresh PowerPivot data failed on the SQL Server Denali machine

I recently encountered an issue. When I try to refresh data for a PowerPivot Excel Spreadsheet on the SQL Server “Denali” machine, I got the ‘SQLNCLI10’ provider is not registered on the local machine error as below:

Issue:

The PowerPivot Excel Spreadsheet was created on a SQL 2008 R2 machine and was loaded with data from a remote SQL 2008 server by using the SQL Native Client 10 provider originally. When I open it on a SQL “Denali” machine that has no SQL Native Client 10 installed, it failed to refresh the data.  The Denali machine only has SQLNCLI11 provider installed.

Solution:

Convert the connection string to use Microsoft OLE DB provider

• Open Excel and click on the PowerPivot window to open the PowerPivot Datasheet

• Go to the Design tab and click on the Existing Connections button to open the Existing Connections window

• Select the PowerPivot Data Connections that used by the datasheet and click on the Edit button.

• On the Edit Connection window, click on the Advanced button

• Change the Providers from SQL Server Native Client 10.0 to Microsoft OLE DB provider for SQL Server from the Drop down menu. 

• Click the OK button and Save the connection change.

Now I could refresh the PowerPivot data on the machine that has SQL Server Denali installed. The Microsoft OLD DB provider for SQL Server should be the best choice to use in order to share the worksheet in different environments.

Note: I tried to install the SQL Server Native Client 10.0 provider and use it on the SQL Server Denali machine or tried to use SQL Server Native Client 11.0 provider showing as below, both methods did not work.

View the error detail for SharePoint unexpected error

During setting up a SharePoint 2010 development machine, I often experience a generic SharePoint error (A dialog box with “An unexpected error has occurred.” message) that telling me nothing but error. Most naive SharePoint developer/designer/user will have no clue what is wrong and how to correct it. Sometime there is not enough information could be provided to the administrator or ask for help.

 

Derek Sanderson recently posted a blog entry about how to get the REAL error to show on web page which helped me a lot to view the error detail on the screen, so I could Google/fix it without dig into the logs files that I may not have permission with.

Since there are two web applications, one for the SharePoint default site, another is the SharePoint Central Admin, I modified the web.config file on both sites accordingly.  I will only leave those settings on for a development machine or sandbox/QA servers.

You may also want to check this out now:

http://dereksan.blogspot.com/2011/02/sharepoint-20072010an-unexpected-error.html

Cannot Display the List in Datasheet View

I recently set up and configured a SharePoint 2010 Enterprise Server on a VMware with Windows 2008 R2 Server as a development machine. The SharePoint Server has both Excel Services and PowerPivot Services installed and running without issues.  During testing the functionality of the SharePoint Server, I encountered the follow error while clicking on the Datasheet View button on the List Tools:
The list cannot be displayed in Datasheet view for one or more of the following reasons:

• A datasheet component compatible with Microsoft SharePoint Foundation is not installed.
• Your Web browser does not support ActiveX controls.
• A component is not properly configured for 32-bit or 64-bit support.

This error indicated a component that required to display the Datasheet View is missing. Since I did not install any Microsoft Office Clients on the server such as Microsoft Word or Microsoft Excel, I will need to install the 32-bit version of Microsoft Office System Driver: 2007 Office System Driver: Data Connectivity Components to enable the Datasheet component.
After the component installed, the list could be displayed as a datasheet now.  This solution will work with the 64-bit version of Office 2010 as long as I am using a 32-bit version of an Internet browser.
  
Note:  If you are using a 64-bit version of an Internet browser, for example the 64-bit version of Microsoft Internet Explorer 8.0, there is no support for the Edit in Datasheet feature.

More information about this issue could be found:

• http://support.microsoft.com/kb/909506 
• http://support.microsoft.com/kb/2266203

Unable to open Excel workbook as it does not contain any visible named items

Recently I setup the Excel Services on SharePoint 2010 Enterprise and posted an Excel workbook to the PowerPivot Gallery. But when I tried to open it on the portal, the following error displayed:

This is a very common error produced by Office Web Applications.  When saving an Excel workbook to a SharePoint site, you can decide to save the complete workbook or only specific Named Items (includes sheets, named cells or ranges, tables, PivotTables, or charts). This is different than saving the workbook to the file system.  By default the Named Items option are not enabled or needed in direct Excel interaction. However when you uploading the workbook to SharePoint, you must edit the Publish Option (to select the entire worksheet or Individual sheet) before you Save & Sent to the SP site.
There are two ways to edit the option as showing below:

 

Once you set the options, you should be able to view the published workbook on the SharePoint portal now.