Concatenation Stops At 500 Chars (argh!)
-
I am using SQL Server 2000. I have a need to concatenate character fields so that the resulting character string is more than 500 characters, but is less than 8000. My actual query is more complicated than what I show below, but I boiled the problem down to the script so that the other stuff doesn't get in the way of the actual problem. Despite defining the output variable (@out) as varchar(8000), the output never goes beyond 500 characters. Using char(8000) didn't seem to help either. What to do?
I've been searching the internet for days on this problem and can't find reference to it, but I'd swear I read an article about this problem somewhere. Does anyone have any ideas? Your help would keep me from going bald! Many thanks.
EXAMPLE SCRIPT:
declare @out varchar(8000),
@st1 varchar(8000),
@st2 varchar(8000),
@st3 varchar(8000)
SELECT --@out = '',
@st1 = 'a) These words by themselves are about 410 characters. This is a good way to illustrate the problem I am having with concatenation. SQL Server stops at 500 characters no matter how I define the variable - as either varchar(8000) or char(8000). 500 is a magic number, 2x255. So, I think I know why it stops at 500. It is somehow related to the old restriction on character variables. But what about the 8000? ',
@st2 = 'b) These words by themselves are about 410 characters. This is a good way to illustrate the problem I am having with concatenation. SQL Server stops at 500 characters no matter how I define the variable - as either varchar(8000) or char(8000). 500 is a magic number, 2x255. So, I think I know why it stops at 500. It is somehow related to the old restriction on character variables. But what about the 8000?',
@st3 = 'c) These words by themselves are about 410 characters. This is a good way to illustrate the problem I am having with concatenation. SQL Server stops at 500 characters no matter how I define the variable - as either varchar(8000) or char(8000). 500 is a magic number, 2x255. So, I think I know why it stops at 500. It is somehow related to the old restriction on character variables. But what about the 8000?'
SELECT @out = @st1 + char(13) + @st2 + char(13) + @st3
select @out AS BadOutput_ItsTruncated
--(changing from varchar() to char() doesn't help - at least the way I tried it)
OUTPUT LOOKS LIKE THIS:
BadOutput_ItsTruncated
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a) These words by themselves are about 410 characters. This is a good way to illustrate the problem I am having with concatenation. SQL Server stops at 500 characters no matter how I define the variable - as either varchar(8000) or char(8000). 500 is a magic number, 2x255. So, I think I know why it stops at 500. It is somehow related to the old restriction on character variables. But what about the 8000?
b) These words by themselves are about 410 characters. This is a good way to illustrat
(1 row(s) affected)
-
Fixed! Wouldn't you just know it. After strugling forever with this problem, I figured out just one minute after posting this post that the problem was that under Tools, Options, the box 'Maximum characters per column' was set to 500. So, of course my results stopped at 500 characters. Hopefully the output of my stored procedure will not stop at 500 and all will be well with the world (and my head).
Sorry to bother everyone. But maybe this posting will help someone else.
-
*was too late
-
FWIW, when in doubt of what you see is what you really get, you could also do some 'sanity checks' on the material..
select len(@out) or select datalength(@out)
...would probably have indicated that there was something beyond 500 in there,
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply