8000 character limit in OPENQUERY against Analysis Server

I have a query like

SELECT column1, column2 FROM OPENQUERY(AnalysisServerName, 'MDX QUERY ...').

It is inside a stored procedure. A MDX query is dynamically built in it and the length of the query much longer than 8000 characters (can reach up to 400 000 signs).

The MDX query returns something about 200 columns and I need only some of them. I am not able to reduce the amount because of dimensions complexity.

So, I have to write results to a temporary table or directly SELECT them as the result of the procedure. I decided to SELECT them. But, when I try to omit the limitation of 8000 characters with EXEC OPENQUERY(AnalysisServerName, 'MDX QUERY ...') AT AnalysisServerName, I can't SELECT or even save them to a temporary table as it happens inside the stored procedure and nesting is not allowed (following this thread).

I wish I can do

SELECT column1, column2 FROM (EXEC OPENQUERY(AnalysisServerName, 'MDX Query') AT AnalysisServerName)

Even if I can execute a very long MDX query directly on Analysis Server

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'OPENQUERY(AnalysisServerName, 'MDX Query')'

EXECUTE (@myStatement) AT AnalysisServerName

I can't do anything with the results in my stored procedure, because

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'SELECT column1, column2 FROM OPENQUERY(AnalysisServerName, 'MDX Query')'

EXECUTE (@myStatement) AT AnalysisServerName

is not allowed.

So what can I do in such situation?

I need to

• query Analysis Service with OPENQUERY,

• handle the query of length greater than 8000,

• save the results to a temporary table or select them directly.

I need to

• query Analysis Service with OPENQUERY,

• handle the query of length

greater than 8000,

• save the results to a temporary table or select

them directly. sql-serverssasmdxEdit tags

Instead of OPENQUERY use INSERT ... EXEC ... AT LinkedServer, like this:

create table #t(id int, a int)

insert into #t
exec ('select 1 id, 2 a') at LinkedServer

select * from #t

Here's a sample using SSAS and MDX:

USE [master]
go

/****** Object:  LinkedServer [SSAS]    Script Date: 7/29/2021 11:27:57 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SSAS', @srvproduct=N'', @provider=N'MSOLAP', @datasrc=N'localhost', @catalog=N'Adventure Works Internet Sales'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSAS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation compatible', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'data access', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'dist', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'pub', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'rpc out', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'sub', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'connect timeout', @optvalue=N'0'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'collation name', @optvalue=null
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'lazy schema validation', @optvalue=N'false'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'query timeout', @optvalue=N'0'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'use remote collation', @optvalue=N'true'
go

EXEC master.dbo.sp_serveroption @server=N'SSAS', @optname=N'remote proc transaction promotion', @optvalue=N'false'
go

drop table if exists #t
go
create table #t
(
  [[Date]].[Fiscal Year]].[Fiscal Year]].[MEMBER_CAPTION]]] varchar(20), 
  [[Measures]].[Internet Total Sales]]] decimal(20,2),
  [[Measures]].[Internet Total Tax Amt]]] decimal(20,2)
)

insert into #t
exec ('
SELECT  
    { [Measures].[Internet Total Sales],   
        [Measures].[Internet Total Tax Amt]} ON COLUMNS,  
    { [Date].[Fiscal Year].AllMembers  } ON ROWS  
FROM [Adventure Works Internet Sales Model]  
') at SSAS

select * from #t

StackExchange Database Administrators Q#296287, answer score: 9