| Author |
Topic |
|
spinal007
Starting Member
7 Posts |
 Posted - 2008-12-17 : 11:01:12
|
| Hi there,I've been using SQL2000 for quite a while now and every now and then I come across the very annoying 8k row limit. But lately, the problem I'm facing is that users of my system need to store data larger than 8k in a single column.I've looked into migrating to SQL2005 and read everything about nvarchar(max) - which sounds great in theory but doesn't seem to work in practice.For example, if I declare a variable and populate 20k bytes of data in it, SQL Server 2005 only stores 8000 without even displaying an error message. Here is the code I used to test this out:declare @n nvarchar(max)select @n = replicate('1', 20000)select Len(@n) as '20,000?'-- the select statement return a column with 8000So, my question is: has any one overcome this problem? Is there a way to store more than 8,000 characters in SQL 2000? and does nvarchar(max) really allow you to do this in SQL 2005?Thanks in advanced! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-17 : 11:11:13
|
| what about this?SELECT DATALENGTH(@n) |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-17 : 11:11:22
|
Tried DATALENGTH instead of LEN?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-17 : 11:12:41
|
And yes you can store more than 8000 bytes in SQL Server2000 with TEXT/NTEXT data type.The data is stored off page and only a pointer to storage is kept in page.However, manipulating the content in a TEXT/NTEXT column is tricky.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
spinal007
Starting Member
7 Posts |
Posted - 2008-12-17 : 11:29:32
|
| Hi guys,Thank you so much for the prompt reply.In reply to your suggestions of using the datalength function, that returns 16,000, presumably because 8,000 characters of unicode data take up 16,000 bytes.I tested again with 100,000 characters and the same problem happens:declare @n nvarchar(max)select @n = replicate('1', 100 * 1000)select Len(@n) as '=8,000', datalength(@n) as '=16,000'Having said that, I'm not really bothered about the local variable reporting the wrong length, as long as the data gets stored in the table.BUT, when I try to insert (or update) a record in SQL 2000 and SQL 2005 I get the following error:Msg 8152, Level 16, State 4, Line 1String or binary data would be truncated.The statement has been terminated....and the error happens when I try to insert more than 8,000 characters into a single ntext column. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-17 : 11:35:59
|
| how are you trying to update? pass value from variable or hardcoded value? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-17 : 11:41:20
|
Also, the way you concatenate string has a bug.Use this and even LEN works ok!declare @n varchar(max)set @n = replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) set @n = @n + replicate('1', 100000) select Len(@n) as '=8,000', datalength(@n) as '=16,000'
E 12°55'05.63"N 56°04'39.26" |
|
|
|
spinal007
Starting Member
7 Posts |
Posted - 2008-12-17 : 11:45:49
|
| I have a table called tblAPP that stores name/value pairs of various application settings. Some of them are very large bits of text.I've tried this to insert...insert into tblapp(name,value) values('test1',replicate('x',100*1000))And this to update...update tblapp set value=replicate('x',100*1000) where name='test2'-- there is a single record where name='test2'Now I'm no longer getting a "data would be truncated message" but if then I execute these statements...SELECT datalength(value) FROM where name='test1'SELECT datalength(value) FROM where name='test2'...I get this 16,000 (only the first 8,000 characters are being stored).Thanks for your help so far! |
|
|
|
spinal007
Starting Member
7 Posts |
Posted - 2008-12-17 : 11:50:27
|
| Hi Peso,Your code works (with the concatenation), which is great. But I need to perform the update (or insert) in a single statement. The update is being performed by some code on a website. The code and connection to the server all work fine. But the problem happens as I explained, when I try to store more than 8,000 characters in the 'value' column.I'm sorry if I'm going round in circles but I have looked everywhere for a solution and asking you guys was my last resort. I appreciate you trying to help me with this... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-17 : 12:15:48
|
| http://decipherinfosys.wordpress.com/2007/07/24/update-textntext-or-varcharmax-data-type-columns-in-sql-server/ |
|
|
|
spinal007
Starting Member
7 Posts |
Posted - 2008-12-17 : 12:31:21
|
| Thank you visakh16. It looks like I'll just have to use the .Write clause in SQL2005 when I migrate. the tests provided on that page worked fine... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-17 : 12:46:16
|
ok...welcome  |
|
|
|
bimalfernando
Starting Member
1 Post |
Posted - 2009-10-05 : 00:21:29
|
| Hi Guys,the issue is not with the varchar(max), it is with how you use the REPLICATE method.Try this below to get the correct results. Not the use of cast inside the REPLICATE method.declare @n varchar(max)set @n = replicate(cast ('1' as varchar(max)), 20000)select Len(@n) as '=8,000', datalength(@n) as '=16,000'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. |
|
|
|
|
Storing more than 8000 bytes in a single column