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