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.
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.
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.