Increase length of NVARCHAR(MAX) more than 8000 Character

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 9, 2013 at 7:13 am

  #295405

  Hello Expert,

  Can someone help its very urgent?

  While developing the SSRS report we have to create a stored procedure using MDX query for this we have to hold the MDX string into particular variable but the variable having NVARCHAR(MAX) does not allow string character to be more than 8000 BUT the size of our MDX query string increases while passing multi select Shop parameter value.

  Even the while loop condition on shop parameter does not help us because we have to get Top N value for all the shops and in case of while loop it gives the Top N value of each shop.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 9, 2013 at 7:24 am

  #1630785

  nvarchar(max) holds one or two gb. It's not the problem. There are a number of possible issues here, the most likely is that you are using other variables in the construction of the string, and they are not all nvarchar(max). Can you post a little more detail? Some code?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Lowell

  SSC Guru

  Points: 323504

  More actions

  July 9, 2013 at 7:26 am

  #1630787

  amit_pjoshi (7/9/2013)

  ---

  Hello Expert,

  Can someone help its very urgent?

  While developing the SSRS report we have to create a stored procedure using MDX query for this we have to hold the MDX string into particular variable but the variable having NVARCHAR(MAX) does not allow string character to be more than 8000 BUT the size of our MDX query string increases while passing multi select Shop parameter value.

  Even the while loop condition on shop parameter does not help us because we have to get Top N value for all the shops and in case of while loop it gives the Top N value of each shop.

  NVARCHAR(MAX) supports a huge string 2^31 - 1 bytes(~1+gig nvarchars )...however, many applications, specifically SQL Server Management Studio, will only display the first 8000 characters of the string no matter what the value is

  so if the data is stored in a varchar(max)/nvarchar(max), it defaults to display only the first 256 characters, but if you change the setting pictured below to a largest value, it still will only display the first 8K chars(this is for performance reasons, so grids don't freeze up)

  so the question is, how are you determining the string is only 8000;

  most likely the string is certainly bigger, is stored in a complete fashion, but something you are using to display the data is limiting it to 8000 characters.

  

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 1:45 am

  #1631102

  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

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 2:44 am

  #1631114

  ChrisM@Work (7/9/2013)

  ---

  nvarchar(max) holds one or two gb. It's not the problem. There are a number of possible issues here, the most likely is that you are using other variables in the construction of the string, and they are not all nvarchar(max). Can you post a little more detail? Some code?

  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

• Lowell

  SSC Guru

  Points: 323504

  More actions

  July 10, 2013 at 2:48 am

  #1631116

  instead of

  EXEC sp_executesql @sql

  use

  EXEC(@sql)

  which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be good to go, i think.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 2:54 am

  #1631117

  Lowell (7/9/2013)

  ---

  amit_pjoshi (7/9/2013)

  ---

  Hello Expert,

  Can someone help its very urgent?

  While developing the SSRS report we have to create a stored procedure using MDX query for this we have to hold the MDX string into particular variable but the variable having NVARCHAR(MAX) does not allow string character to be more than 8000 BUT the size of our MDX query string increases while passing multi select Shop parameter value.

  Even the while loop condition on shop parameter does not help us because we have to get Top N value for all the shops and in case of while loop it gives the Top N value of each shop.

  NVARCHAR(MAX) supports a huge string 2^31 - 1 bytes(~1+gig nvarchars )...however, many applications, specifically SQL Server Management Studio, will only display the first 8000 characters of the string no matter what the value is

  so if the data is stored in a varchar(max)/nvarchar(max), it defaults to display only the first 256 characters, but if you change the setting pictured below to a largest value, it still will only display the first 8K chars(this is for performance reasons, so grids don't freeze up)

  so the question is, how are you determining the string is only 8000;

  most likely the string is certainly bigger, is stored in a complete fashion, but something you are using to display the data is limiting it to 8000 characters.

  

  Hi,

  It is just to display the string of 8000 Char but actually my MDX query is making string > 8000 char because of this it does not allow link server to execute MDX query on Analysis server... (You can see more detail on previous response)

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 10, 2013 at 2:59 am

  #1631119

  Lowell (7/10/2013)

  ---

  instead of

  EXEC sp_executesql @sql

  use

  EXEC(@sql)

  which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be good to go, i think.

  Extending this suggestion - you can also execute a string at the remote end with EXECUTE ... AT:

  EXEC('TRUNCATE TABLE mydb.dbo.' + @tablename) AT LinkedServerName

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 3:06 am

  #1631120

  Lowell (7/10/2013)

  ---

  instead of

  EXEC sp_executesql @sql

  use

  EXEC(@sql)

  which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be good to go, i think.

  Hi Lowell,

  Thanks for your quick response.

  Because we are using the link server (OLAP) that will not allow string > 8000 Chars so it will pass the incomplete MDX query to server and give error while EXEC(@sql):

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

  Actually

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 10, 2013 at 3:15 am

  #1631123

  amit_pjoshi (7/10/2013)

  ---

  Lowell (7/10/2013)

  ---

  instead of

  EXEC sp_executesql @sql

  use

  EXEC(@sql)

  which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be good to go, i think.

  Hi Lowell,

  Thanks for your quick response.

  Because we are using the link server (OLAP) that will not allow string > 8000 Chars so it will pass the incomplete MDX query to server and give error while EXEC(@sql):

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

  Actually

  INSERT INTO #MDXResults

  EXECUTE(@mdx) AT OLAP

  INSERT #tblData (Lot, Season, [Value], COGS, Units, Delivered, CountryRank, CountryValue, CountryCOGS,

  CountryUnits, CountryDelivered, SQM, [Shop Model], [Stock], CountryStocks)

  SELECT somecolumns

  FROM #MDXResults

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 6:48 am

  #1631183

  ChrisM@Work (7/10/2013)

  ---

  amit_pjoshi (7/10/2013)

  ---

  Lowell (7/10/2013)

  ---

  instead of

  EXEC sp_executesql @sql

  use

  EXEC(@sql)

  which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be good to go, i think.

  Hi Lowell,

  Thanks for your quick response.

  Because we are using the link server (OLAP) that will not allow string > 8000 Chars so it will pass the incomplete MDX query to server and give error while EXEC(@sql):

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

  Actually

  INSERT INTO #MDXResults

  EXECUTE(@mdx) AT OLAP

  INSERT #tblData (Lot, Season, [Value], COGS, Units, Delivered, CountryRank, CountryValue, CountryCOGS,

  CountryUnits, CountryDelivered, SQM, [Shop Model], [Stock], CountryStocks)

  SELECT somecolumns

  FROM #MDXResults

  Hi Gail,

  We tried the query EXECUTE(@mdx) AT OLAP but it gives the following message

  "Msg 7390, Level 16, State 2, Line 153

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

  CODE As suggested:

  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]'

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

  set @TopNumberParam=20

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

  set @RankBy=N'Value'

  set @DetailLevel = N'C'

  set @Grouping = 'Merchandising Concept'

  set @vat ='incVAT'

  DECLARE

  @mdx nvarchar(max),

  @sql nvarchar(max)

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

  DROP TABLE #tblData

  --Insert Items ranked by value

  BEGIN

  SET @mdx = '

  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 {[Shop].[Shop Model].&[Retail], [Shop].[Shop Model].&[Outlet]} ON COLUMNS

  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]

  )'

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

  DROP TABLE #MdxResult

  CREATE TABLE #MdxResult (

  RankBy varchar(10),

  Lot varchar(100),

  Season varchar(10),

  [Value] money,

  COGS money,

  Units int,

  Delivered int,

  CountryRank int,

  CountryValue money,

  CountryCOGS money,

  CountryUnits int,

  CountryDelivered int,

  SQM float,

  [Shop Model] varchar(255),

  [Stock] int,

  CountryStocks int

  )

  Insert into #MdxResult

  EXECUTE (@mdx) AT OLAP

  select * from #MdxResult

  END

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 6:52 am

  #1631186

  ChrisM@Work (7/10/2013)

  ---

  amit_pjoshi (7/10/2013)

  ---

  Lowell (7/10/2013)

  ---

  instead of

  EXEC sp_executesql @sql

  use

  EXEC(@sql)

  which has no limits on the query size, since it's not parameterized. ou are not passing parameters via sp+executesql, so you'd be good to go, i think.

  Hi Lowell,

  Thanks for your quick response.

  Because we are using the link server (OLAP) that will not allow string > 8000 Chars so it will pass the incomplete MDX query to server and give error while EXEC(@sql):

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

  Actually

  INSERT INTO #MDXResults

  EXECUTE(@mdx) AT OLAP

  INSERT #tblData (Lot, Season, [Value], COGS, Units, Delivered, CountryRank, CountryValue, CountryCOGS,

  CountryUnits, CountryDelivered, SQM, [Shop Model], [Stock], CountryStocks)

  SELECT somecolumns

  FROM #MDXResults

  Hi,

  We tried the query as suggested but gettting following error:

  "Msg 7390, Level 16, State 2, Line 153 The requested operation could not be performed because OLE DB provider "MSOLAP" for linked server "OLAP" does not support the required transaction interface."

  Modified 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]'

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

  set @TopNumberParam=20

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

  set @RankBy=N'Value'

  set @DetailLevel = N'C'

  set @Grouping = 'Merchandising Concept'

  set @vat ='incVAT'

  DECLARE

  @mdx nvarchar(max),

  @sql nvarchar(max)

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

  DROP TABLE #tblData

  --Insert Items ranked by value

  BEGIN

  SET @mdx = '

  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 {[Shop].[Shop Model].&[Retail], [Shop].[Shop Model].&[Outlet]} ON COLUMNS

  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]

  )'

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

  DROP TABLE #MdxResult

  CREATE TABLE #MdxResult (

  RankBy varchar(10),

  Lot varchar(100),

  Season varchar(10),

  [Value] money,

  COGS money,

  Units int,

  Delivered int,

  CountryRank int,

  CountryValue money,

  CountryCOGS money,

  CountryUnits int,

  CountryDelivered int,

  SQM float,

  [Shop Model] varchar(255),

  [Stock] int,

  CountryStocks int

  )

  Insert into #MdxResult

  EXECUTE (@mdx) AT OLAP

  select * from #MdxResult

  END

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 10, 2013 at 6:55 am

  #1631188

  Amit, do you have a BEGIN TRANSACTION / COMMIT TRANSACTION in your code?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 6:59 am

  #1631192

  ChrisM@Work (7/10/2013)

  ---

  Amit, do you have a BEGIN TRANSACTION / COMMIT TRANSACTION in your code?

  Hi Gail,

  No we are not using BEGIN TRANSACTION / COMMIT TRANSACTION.

  we are executing the same code shared with you.

• amit_pjoshi

  Right there with Babe

  Points: 782

  More actions

  July 10, 2013 at 7:03 am

  #1631199

  amit_pjoshi (7/10/2013)

  ---

  ChrisM@Work (7/10/2013)

  ---

  Amit, do you have a BEGIN TRANSACTION / COMMIT TRANSACTION in your code?

  Hi Gail,

  No we are not using BEGIN TRANSACTION / COMMIT TRANSACTION.

  we are executing the same code shared with you.

  Hi Chris apologies for wrong name 🙁