ChrisM@Work (7/10/2013)
amit_pjoshi (7/10/2013)
Lowell (7/10/2013)
instead ofEXEC 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 #MDXResultsEXECUTE(@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