Hi,

I tried your suggestion to use the NVARCHAR(max) to hold the MDX query of more than 8000 chars (upto 2GB) and also changed data type of parameters passing into the MDX query to NVARCHAR(MAX) but it works for relational query only. And when you try to get the data from OLAP database using Linked server and OPENQUERY function the query in the nvarchar(max) variable is reduced to nvarchar(8000). i.e., it can contain only 8000 characters in the openquery function.

For reference :-

http://msdn.microsoft.com/en-us/library/ms188427.aspx

http://stackoverflow.com/questions/8151121/execute-very-long-statements-in-tsql-using-sp-executesql

code:-

DECLARE

@TopNumberParam nvarchar(Max),

@ArticleFilter nvarchar(Max),

@Stores nvarchar(Max),

@FiscalTime nvarchar(Max),

@Currency nvarchar(Max),

@RankBy nvarchar(Max),

@DetailLevel nvarchar(Max),

@Grouping nvarchar(Max),

@vat nvarchar(Max)

set @ArticleFilter=N'[Articles].[SKU].[All]'

set @Stores='[Shop].[Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D2],[Shop].[Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D6],[Shop].[Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D1],[Shop].[Shop by Model].[Brand].&[7FAM].&[Outlet].&[0ZW],[Shop].[Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D4],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0KN],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0BA],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0D9],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0BJ],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0D8],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0DC],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0DR],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0DB],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0D7],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[07U],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0D3],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0DA],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0DH],[Shop].[Shop by Model].[Brand].&[7FAM].&[Retail].&[0BF],[Shop].[Shop by Model].[Brand].&[VANS].&[Outlet].&[0SG],[Shop].[Shop by Model].[Brand].&[VANS].&[Outlet].&[0SS]'

set @FiscalTime=N'[Time].[Fiscal Hierarchy].&[2012031]'

set @TopNumberParam=20

set @Currency=N'[Reporting Currency].[Currency].&[EUR]'

set @RankBy=N'Units'

set @DetailLevel = N'C'

set @Grouping = 'Merchandising Concept'

set @vat ='incVAT'

IF OBJECT_ID('tempdb.dbo.#tblData') IS NOT NULL

DROP TABLE #tblData

--Main data table

CREATE TABLE #tblData (

RankByvarchar(10),

Lotvarchar(100),

Seasonvarchar(10),

[Value]money,

COGSmoney,

Unitsint,

Deliveredint,

CountryRankint,

CountryValuemoney,

CountryCOGSmoney,

CountryUnitsint,

CountryDeliveredint,

SQMfloat,

[Shop Model]varchar(255),

[Stock]int,

CountryStocksint

)

--Insert Items ranked by value

DECLARE @mdx nvarchar(max), @sql nvarchar(max),@mdx1 nvarchar(max),@sql1 nvarchar(max)

BEGIN

SET @mdx = N'WITH

SET TopSellers AS TopCount(NonEmpty(iif("' + @Grouping + '"="Lot" or "' + @Grouping + '"="Style",[Articles].[' + @Grouping + '].[' + @Grouping + ']*[Articles].[Season].[Season],[Articles].[' + @Grouping + '].[' + @Grouping + ']),[Measures].[Stores2 Sales Quantity]),' + @TopNumberParam + ',iif("'+ @vat +'"= "incVAT",[Measures].[Stores2 Sales Value Net inc VAT - Base],[Measures].[Stores2 Sales Value Net exc VAT - Base]))

MEMBER [Measures].[TopSellersUnits]AS Sum(TopSellers,[Measures].[Stores2 Sales Quantity])

MEMBER [Measures].[Season] AS [Articles].[Season].CURRENTMEMBER.MEMBER_CAPTION

SET Countries AS Iif("'+ @DetailLevel +'"= "C",NonEmpty([Shop].[Country Group].Members,[Measures].[TopSellersUnits]),NonEmpty(([Shop].[Shop].members,strtoset("{'+ @Stores +'}")),[Measures].[TopSellersUnits]))

MEMBER [Measures].[Value] AS Iif("'+ @vat +'"= "incVAT",[Measures].[Stores2 Sales Value Net inc VAT - Base],[Measures].[Stores2 Sales Value Net exc VAT - Base])

MEMBER [Measures].[COGS] AS [Measures].[Stores2 Sales Cost - Base]

MEMBER [Measures].[Units] AS [Measures].[Stores2 Sales Quantity]

MEMBER [Measures].[Delivered] AS ([Measures].[Stores2 Sales Quantity],[Time].[Fiscal Hierarchy].[All],[TransactionType].[Transactiontype].&[D])

MEMBER [Measures].[CountryRank] AS Rank(iif("' + @Grouping + '"="Lot" or "' + @Grouping + '"="Style",([Shop].[Country Group].CURRENTMEMBER,[Articles].[' + @Grouping + '].CURRENTMEMBER,[Articles].[Season].CURRENTMEMBER),([Shop].[Country Group].CURRENTMEMBER,[Articles].[' + @Grouping + '].CURRENTMEMBER)),Order(NonEmpty([Shop].[Country Group].CURRENTMEMBER*iif("' + @Grouping + '"="Lot" or "' + @Grouping + '"="Style",[Articles].[' + @Grouping + '].[' + @Grouping + ']*[Articles].[Season].[Season],[Articles].[' + @Grouping + '].[' + @Grouping + ']),[Measures].[Stores2 Sales Quantity]),(iif( "'+ @vat +'"= "incVAT",[Measures].[Stores2 Sales Value Net inc VAT - Base],[Measures].[Stores2 Sales Value Net exc VAT - Base]),' + @ArticleFilter + '),BDESC))

MEMBER [Measures].[CountryValue] AS (iif( "'+ @vat +'"= "incVAT",[Measures].[Stores2 Sales Value Net inc VAT - Base],[Measures].[Stores2 Sales Value Net exc VAT - Base]),[Articles].[' + @Grouping + '].[All], ' + @ArticleFilter + ')

MEMBER [Measures].[CountryCOGS] AS ([Measures].[Stores2 Sales Cost - Base],[Articles].[' + @Grouping + '].[All],' + @ArticleFilter + ')

MEMBER [Measures].[CountryUnits] AS ([Measures].[Stores2 Sales Quantity],[Articles].[' + @Grouping + '].[All],' + @ArticleFilter + ')

MEMBER [Measures].[CountryDelivered] AS ([Measures].[Stores2 Sales Quantity],[Articles].[' + @Grouping + '].[All],' + @ArticleFilter + ',[Time].[Fiscal Hierarchy].[All],[TransactionType].[Transactiontype].&[D])

MEMBER [Measures].[SQM]AS [Measures].[Stores2 Shop SQM Net]

MEMBER [Measures].[Shop Model] AS Iif("'+ @DetailLevel +'"= "C",[Shop].[Country Group].CURRENTMEMBER.MEMBER_CAPTION,[Shop].[Shop].CURRENTMEMBER.MEMBER_CAPTION)

MEMBER [Measures].[Stock] AS Iif([Measures].[Units]<=0,"",[Measures].[Stores2 History Inventory Physical Quantity])

MEMBER [Measures].[CountryStocks] AS ([Measures].[Stores2 History Inventory Physical Quantity],[Articles].[' + @Grouping + '].[All], ' + @ArticleFilter + ')

MEMBER [Measures].[GroupingParam] AS [Articles].[' + @Grouping + '].CURRENTMEMBER.MEMBER_CAPTION

SELECT {[Measures].[GroupingParam],[Measures].[Season],[Measures].[Value],[Measures].[COGS],[Measures].[Units],[Measures].[Delivered],[Measures].[CountryRank],[Measures].[CountryValue],[Measures].[CountryCOGS],[Measures].[CountryUnits],

[Measures].[CountryDelivered],[Measures].[SQM],[Measures].[Shop Model],[Measures].[Stock],[Measures].[CountryStocks]} ON COLUMNS,

{Countries}*{TopSellers} ON ROWS

FROM(SELECT {strtoset("{' + @Stores + '}")}ON COLUMNS FROM VFE)

WHERE(' + @Currency + ',' + @ArticleFilter + ',' + @FiscalTime + ',[TransactionStatus].[Transactionstatus].&[0],[TransactionType].[Transactiontype].&,{[Store Transaction Motive].[Store Transaction Motive].&[U+], [Store Transaction Motive].[Store Transaction Motive].&[U-]},[Store Transaction Suspended].[Store Transaction Suspended].&[False] )'

--Construct sql string to insert OLAP results into temp table

SET @sql = N'

INSERT #tblData ( Lot, Season, [Value],COGS, Units, Delivered, CountryRank, CountryValue, CountryCOGS, CountryUnits, CountryDelivered, SQM, [Shop Model], [Stock], CountryStocks)

SELECT

"[Measures].[GroupingParam]",

"[Measures].[Season]",

"[Measures].[Value]",

"[Measures].[COGS]",

"[Measures].[Units]",

"[Measures].[Delivered]",

"[Measures].[CountryRank]",

"[Measures].[CountryValue]",

"[Measures].[CountryCOGS]",

"[Measures].[CountryUnits]",

"[Measures].[CountryDelivered]",

"[Measures].[SQM]",

"[Measures].[Shop Model]",

"[Measures].[Stock]",

"[Measures].[CountryStocks]"

FROM OPENQUERY(OLAP, ''' + @mdx + ''')'

BEGIN TRY

EXEC sp_executesql @sql

END TRY