4

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.

Community
  • 1
VS1SQL
  • 41
  • 3

1 Answers1

-1

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

CR241
  • 1,565
  • 3
  • 18
  • 32