Sql-server – Get data from a MDX linked server and use it on a FROM clause on SQL without OPENQUERY

sql serversql-server-2012

i have the following SQL query on SQL Server 2012:

WITH MdxQuery (field1,field2,field3) AS (
    SELECT field1, field2, field3
    FROM OPENQUERY(my_linked_server, 'my_long_mdx_query')
)
SELECT * FROM MdxQuery

The 'my_long_mdx_query' is a MDX query running on a linked server. Everything works fine except when the MDX query is longer than 8000 characters, which is unsupported by OPENQUERY.

I have tried to use variables, but without success:

Declare @TempTable TABLE (
     field1 varchar(max),
     field2 varchar(max),
     field3 varchar(max)
)
Declare @strMDX varchar(max)
set @strMDX = N'my_long_mdx_query'
INSERT INTO @TempTable (
    field1,
    field2,
    field3
) EXEC (@strMDX) at my_linked_server

WITH MdxQuery (field1,field2,field3) AS (
    SELECT field1, field2, field3
    FROM @tempTable
)
SELECT * FROM MdxQuery

It throws this error before reaching the WITH MdxQuery part:

The requested operation could not be performed because OLE DB provider "MSOLAP" for linked server "my_linked_server" does not support the required transaction interface.

Is it any missing configuration? Is there any other approache i can use?
Thanks

Best Answer

If you're familiar with SSIS it can be done this way by saving the query in a file and then using it as a source query. You'll start by creating an OLE DB connection manager that uses the Microsoft OLE DB Provider for Analysis 13.0 provider. Set the initial catalog to your cube. Then add an Execute SQL Task with the OLE DB connection. For the SQLSourceType, use file connection and create a connection manager for the file with the saved MDX query (or select this connection manager if you already created it). From here, you can output the results to a table. One option is to store the intermediate results in an SSIS object variable. To do this you'll need to set the Result Set property to Full, and map the object variable to result name 0 on the result set pane of the Execute SQL Task. You can then put another Execute SQL Task inside a Foreach Loop (ADO Enumerator type), map variables for each of the columns returned by the query, and then use these variables for an insert into an output table with the Execute SQL Task that's inside the Foreach Loop.

Related Question