I know. While everyone else is moving on to SQL 2012 here I am upgrading a SSAS 2005 cube to SQL Server 2008 R2 Analysis Services. I can hear Garth imploring me to “Live in the now man!“

So as I went about the mundane task of upgrading a cube from a really old version to a not so old version: WHAM! Failure!

Internal error: The operation terminated unsuccessfully.
Server: The current operation was cancelled because another operation in the transaction failed.
Errors in the back-end database access module. The provider ‘SQLNCLI.1’ is not registered.
The following system error occured: Class not registered
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of yaddayadda
Errors in the OLAP storage engine: An error occured while the dimension with the ID of blahblah
Errors in the OLAP storage engine: An error occured while the x attribute of the y dimension

Huh. Didn’t see that coming.

So I dutifully tested my connection. Found it worked and started scratching my head. Then buried deep in the bowels of the MSDN forums I spotted the answer (conveniently not labeled as the answer).

It turns out that when the upgrade occurs you end up with the wrong SQL Native Provider. Open up your data source designer and examine the connection string. If you see SQLNCLI.1 you have just stumbled on the source of your problem. (Yes, I’m assuming you’ve got the same problem as me since that’s more than likely the only reason you’re still reading this masterpiece of technical literature).

Hit the Edit connection string button and take a look at the Provider drop down. Native OLE DB\SQL Native Client? There’s your issue.

Hit the provider drop down and select “SQL Server Native Client 10.0” (or presumably SQL Server Native Client 11.0 if you’re really hip).

That should be it my friend. Hit OK and process your cube and you should be good.

But wait! When’s the last time you watched a Wayne’s World clip? How about the very one where Garth says “Live in the now!” Well thanks to the miracle of YouTube here it is: