Write to VARCHAR(MAX)
Ok, this is probably really simple but.... how the hell do I write more than 8000 characters to a VARCHAR(MAX) in Managament Studio on SQL 2008?
If I use REPLICATE to write an arbitary string of 10000 characters and it's fine. If i try and write any actual data it trims it to 8000 characters.
If I use REPLICATE to write an arbitary string of 10000 characters and it's fine. If i try and write any actual data it trims it to 8000 characters.
Varchar is limited. Use Text instead of the Varchar.
How do you know it trims them?
this works fine for me
declare @x varchar(max)
set @x = replicate('x', 8000)
set @x = @x + replicate('y', 8000)
print right(@x, 50)
print len(@x)
declare @x varchar(max)
set @x = replicate('x', 8000)
set @x = @x + replicate('y', 8000)
print right(@x, 50)
print len(@x)
ASKER
Because using SELECT DATALENGTH([MyColumn]) returns 8000, and only the first 8000 characters are being pulled back to my app.
do you get all y's for the first line and 16000 for the 2nd above?
datalength works fine for me too.
datalength works fine for me too.
ASKER
Yes. As I said in my original question, writing to the column using REPLICATE works fine, it's just writing any actual real data that is being truncated.
Can you show me some of the code you're using where the data gets trimmed?
and, do not use text...it will go away some day.
and, do not use text...it will go away some day.
ASKER
Basically:
UPDATE [SomeTable] SET [SomeColumn] = 'some string with more than 8000 characters' WHERE [somecolumn] = 'some value'ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please verify that all variables / parameters used to hold your varchar(max) value are defined as varchar(max).
ASKER
OK, after some experimenting, it appears to be an issue with the fact that I am embedding CHAR(13) and CHAR(9) into the data i am trying to write. So, how do I write to a VARCHAR(MAX) including those two characters?
hmmm...that is interesting. can you test to see if it works with a nvarchar(max) field?
ASKER
Actually scratch that. Instead of:
'some text' + CHAR(13) + CHAR(9) + 'blah blah''some text' + CAST(CHAR(13) + CHAR(9) AS VARCHAR(MAX)) + 'blah blah'
yeah, Im not surprised though. Some of the ways it handles character values are strange.
ASKER
Not exactly a solution, but a very useful sanity check :)