Thursday, September 12, 2013

OLEDB vs ADO.Net error in SSAS Tabular Models

OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.

So I come into the office pretty early, with a spring in my step, definitely amped up for the day ahead. I mean, who wouldn't be, especially if you are working with Tabular Models is SSAS. That aside...because ofttimes it can be short lived as was my experienced today. I try to load my table into the designer so I can start performing some DAX magic. Let's agree, DAX is magic!!! Comprendo? Good.

When I go into table properties and click "OK". I get an OLEDB or ODBC error.

The error reads as such:
OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001.
A connection could not be made to the data source with the DataSourceID of 'c11c7644-9785-4e7f-89bb-e227848edd62', Name of '<Data Source Name>'.

An error occurred while processing the partition 'PARTITION_NAME_b799baff-060f-4e30-8dcc-bd8aff962b25' in table 'PARTITION_NAME_b799baff-060f-4e30-8dcc-bd8aff962b25'.
The current operation was cancelled because another operation in the transaction failed.




This error is caused by ADO.Net, so I had to change my provider from SQL Server Native Client to OLEDB Provider for SQL Server.

I don't know why this happens, but it does. If at first the change doesn't seem to work. Just close and reopen the project because connections are cached. You already know how to change providers, but here goes anyway,...