Sql-server – OLAP linked server fails connection test

connectivitylinked-servermdxsql serverssas

I am trying to create a new linked server to an Analysis Services database and I would like to use this linked server to import data into a SQL Server table.

example:

SELECT *   
into raw.example
FROM openquery( Test1, 'MDX CODE/etc etc'

Below is a screenshot of what I have done (just putting in examples/test names instead of the names I need to use). The data source is pointing to a valid server name, which I know I have access to.

enter image description here

After I click "ok" to create this linked server this message comes up:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

OLE DB provider 'MSOLAP' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

I am not sure why it isn't connecting. Is it a server issue where I have to ask for permission to access it, or do I somehow need to revert away from the provider being configured to run in a single-threaded apartment mode?

I am doing this on my local SQL Server Express Edition machine, whereas before my script and OPENQUERY/MDX query worked on a remote desktop server etc.

I have looked at other posts but those solutions do not solve my problem.

If it matters, the MDX I'm trying to execute was generated by XLCubed.

Best Answer

Enable OLEDB Driver in SQL Server 

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;  
GO  
RECONFIGURE;  
GO

If you are still facing the error after running above statement, please post error message

Related Question