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
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
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: 16180 Visits: 19543
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28105 Visits: 39939
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
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
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
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/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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28105 Visits: 39939
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
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
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
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: 16180 Visits: 19543
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
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
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
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: 16180 Visits: 19543
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
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