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.

Share Excel Workbooks on SharePoint Server 2010

You can reuse and share Excel workbooks on SharePoint Server 2010 portals and dashboards by using Excel Services.   This means that you can control what data is displayed and maintain a single version of your Excel workbook.
Excel Services supports workbooks that are connected to external data sources. By saving them centrally in a data connection library file on the SharePoint Site, other people could also use the same connection method to view and interact with your data.
To do that you will need to export the data source connection files to SharePoint Site as following:

• Open the excel
• Go to Data tab and click on the Connection Properties menu for your worksheet

• 
  

• Click on the Definition tab and then click on the Export Connection File button on the bottom 
• Save the file on a SharePoint site with Office Database Connection extension (*.odc)

• Once the data connection file been saved on the SharePoint site, the location of the connection file should be changed and pointed to the SharePoint Site

• Now you could Save and Publish your Workbook to SharePoint site by Click on the Save & Send  menu

Once you did that, other people from your team/company can view live, interactive workbooks you created by using the browser on the SharePoint site.

SSIS Error Loading Excel Data with Office 2010 installed

Recently I am setting up a virtual machine to play with the new SQL Server “Denali”. The latest Office 2010 applications and Visual Studio 2010 also have been installed on it.  I open a SSIS project and create an Excel Connection Manager with default version set to Microsoft Excel 2007 to load a excel spreadsheet that was saved as *.xlsx format .
When I  use this newly created Excel Connection Manager as the Data Source in my data flow, I got the following error after click the Preview button:

The SSIS error log has following:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
People will normally assume that the machine should contains the latest data provider (Office 2007 provider(12)), when the Office 2010 Excel/Access application installed on the machine. Unfortunately, it is not the case.

There are two ways to resolve this issue:

1. Install the latest office driver form the following link: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
2. Save the Excel spreadsheet to be Microsoft Excel 97-2003 (*.xls) format to load the data in.

Prepare Microsoft 70-452 Certification Exam

Recently I want to get the Microsoft 70-452 certification, so I could become a Microsoft Certified IT Professional (MCITP) for Business Intelligence Developer 2008.  There is a pr-requirement for this exam which I already had:
Exam 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
After I search the internet for free/cheap training materials, I find the following:

1. A free 6-part webcast series to prep for 70-452 available from the Microsoft Partner Learning Center:  https://training.partner.microsoft.com/learning/app/management/LMS_LearnerHome.aspx
2. A free 12 hours self-paced course: Essential SQL Server 2008 For Developers from Microsoft e-learning: https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=166269

To me, the first webcast is introduction type information and it is very generic. It is suitable for people who are new to SQL 2008 BI.  If you are expert or advanced BI developer, you may not want to waste you time on it. I did find the 2nd course very helpful.  Since I don’t have time and I feel I am expert in some area, I skip the first several topics and only review those that I am not familiar with such as Data Mining for SQL 2008, Understanding SSAS Query & Performance Improvements, etc.. Lots contents in there are very helpful for 70-452 exam.
I also use Transcender’s practice material: http://www.transcender.com/practice-exam/microsoft/70-452.kap. It is a very useful tool to simulate the actual certification exam and provides validated answers.
After studying for a week using above materials, I passed the exam and got the MCITP.
Hope you will find this information useful.