SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Increase length of NVARCHAR(MAX) more than 8000 Character


Increase length of NVARCHAR(MAX) more than 8000 Character

Author
Message
amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 78
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].&[S],
{[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
amit_pjoshi
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 78
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].&[S],
{[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
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16208 Visits: 19544
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
Exploring Recursive CTEs by Example Dwain Camps
amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 78
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
amit_pjoshi
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 78
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 Sad
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16208 Visits: 19544
amit_pjoshi (7/10/2013)
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 Sad


You don't need to apologise to me - maybe Gail ;-)

Have a quick read through this article. It may actually describe the problem and a fix.

“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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search