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 Tuesday, July 9, 2013 7:13 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
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.
Post #1471602
Posted Tuesday, July 9, 2013 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1471606
Posted Tuesday, July 9, 2013 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471608
Posted Wednesday, July 10, 2013 1:45 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
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
Post #1471993
Posted Wednesday, July 10, 2013 2:44 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/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 (
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 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].&[S],{[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
Post #1472008
Posted Wednesday, July 10, 2013 2:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472010
Posted Wednesday, July 10, 2013 2:54 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
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)
Post #1472011
Posted Wednesday, July 10, 2013 2:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1472013
Posted Wednesday, July 10, 2013 3:06 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
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
Post #1472014
Posted Wednesday, July 10, 2013 3:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1472018
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse