September 28, 2012 at 9:27 am
Ok, what am I doing wrong here?
declare @nblah nvarchar(max)
set @nblah = convert(nvarchar(max), '')
set @nblah = cast(replicate('0', 9000) as nvarchar(max))
select len(@nblah), datalength(@nblah)
This results in:
8000, 16000
Doesn't matter what I change the replicate value to, the results do not change when higher than 8000.
September 28, 2012 at 9:31 am
You can make it longer by concatenating strings:
declare @nblah nvarchar(max)
set @nblah = convert(nvarchar(max), '')
set @nblah = cast(replicate('0', 8000) as nvarchar(max)) + cast(replicate('0', 1000) as nvarchar(max))
select len(@nblah), datalength(@nblah)
September 28, 2012 at 9:32 am
In this example, I could. In my real-world issue, this is not an option. What is the actual problem with this?
September 28, 2012 at 9:55 am
Its something like:
Varchar(max) can hold 2 GB, but the string(s) you load it with can only hold 8000 bytes, so you have to concatenate them to get the (max) size...
Even though you've cast the string as varchar(max)
cast(replicate('0', 9000) as nvarchar(max))
the value you've cast is not varchar(max)
replicate('0', 9000)
So it ends up as varchar(max), but it was only varchar(8000) when you populated it.
That's my understanding anyway. đŸ™‚
September 28, 2012 at 10:01 am
Hi,
Doing this
DECLARE @STR NVARCHAR (MAX);
;WITH CTE_Count AS
(
select counter = 1
union all
select counter = counter+1
from CTE_Count
Where counter <= 14000
)
SELECT
@STR=COALESCE(@str + '0', '')
from
CTE_Count
Option (MAXRECURSION 0)
select len(@str), datalength(@str)
The output is:
1400028000
So as you can see there is no 8000 limit...
Don't know why your query only outputs 8000...
I'll look a little deeper..
Regards,
Pedro
September 28, 2012 at 10:04 am
PiMané (9/28/2012)
Hi,Doing this
DECLARE @STR NVARCHAR (MAX);
;WITH CTE_Count AS
(
select counter = 1
union all
select counter = counter+1
from CTE_Count
Where counter <= 14000
)
SELECT
@STR=COALESCE(@str + '0', '')
from
CTE_Count
Option (MAXRECURSION 0)
select len(@str), datalength(@str)
The output is:
1400028000
So as you can see there is no 8000 limit...
Don't know why your query only outputs 8000...
I'll look a little deeper..
Regards,
Pedro
This column starts off as Nvarchar(Max) & the data is concatenated in - so that's why it works.:-)
September 28, 2012 at 10:07 am
laurie-789651 (9/28/2012)
This column starts off as Nvarchar(Max) & the data is concatenated in - so that's why it works.:-)
Yep... like laurie says somewhere in your query the data isn't nvarchar(MAX)...
change it to:
declare @nblah nvarchar(max)
set @nblah = convert(nvarchar(max), '')
set @nblah = cast(replicate(CAST('0' AS NVARCHAR(MAX)), 9000) as nvarchar(max))
select len(@nblah), datalength(@nblah)
it works fine...
September 28, 2012 at 10:12 am
PiMané (9/28/2012)
laurie-789651 (9/28/2012)
This column starts off as Nvarchar(Max) & the data is concatenated in - so that's why it works.:-)Yep... like laurie says somewhere in your query the data isn't nvarchar(MAX)...
change it to:
declare @nblah nvarchar(max)
set @nblah = convert(nvarchar(max), '')
set @nblah = cast(replicate(CAST('0' AS NVARCHAR(MAX)), 9000) as nvarchar(max))
select len(@nblah), datalength(@nblah)
it works fine...
You might need to be a bit careful with that... Using Max has an overhead. Best test it carefully.
September 28, 2012 at 10:33 am
coronaride (9/28/2012)
Ok, what am I doing wrong here?
declare @nblah nvarchar(max)set @nblah = convert(nvarchar(max), '')
set @nblah = cast(replicate('0', 9000) as nvarchar(max))
select len(@nblah), datalength(@nblah)
This results in:
8000, 16000
Doesn't matter what I change the replicate value to, the results do not change when higher than 8000.
From BOL's documentation of replicate http://msdn.microsoft.com/en-us/library/ms174383.aspx
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.
Your CAST is in the wrong place - try it this way:
declare @nblah nvarchar(max)
set @nblah = replicate(cast('0' as nvarchar(max)), 9000)
select len(@nblah), datalength(@nblah)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 28, 2012 at 10:43 am
Nice. Thank you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply