Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Increase length of NVARCHAR(MAX) more than 8000 Character Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 6:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
Points: 28, 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
Post #1472092
Posted Wednesday, July 10, 2013 6:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
Points: 28, 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
Post #1472096
Posted Wednesday, July 10, 2013 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,858, Visits: 14,148
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
Post #1472099
Posted Wednesday, July 10, 2013 6:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
Points: 28, 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.

Post #1472103
Posted Wednesday, July 10, 2013 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
Points: 28, 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
Post #1472111
Posted Wednesday, July 10, 2013 7:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 6,858, Visits: 14,148
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


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
Post #1472115
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse