Not able to save more than 43679 char in text datatype column in SQL Server

Question

I have a column in table with text datatype and trying to save some string value to this column from C# code. Issue comes when I use some very large string.

I am not able to save more than 43679 character into text field. I know text size can be 2^31.

I also tried saving same value from SSMS and noticed same scenario.

There is nothing special about code, but still SQL query is given below...

update TableName
set ColumnName = 'some text more than 43679 char'
where id=<some int id>

just to mention... column is declare in table as

[columnname] [text] NULL

Can anyone tell me what could be wrong.

`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, Nov 03 '15 at 09:43
Thanks for info.... I'll update my column with varchar(max).. — Abhash786, Nov 03 '15 at 09:52
Related: https://stackoverflow.com/questions/25709069/the-maximum-number-of-characters-a-textbox-can-display — Thomas Weller, Dec 26 '21 at 19:48
Related: https://stackoverflow.com/questions/14355427/is-43679-a-magic-number — Thomas Weller, Dec 26 '21 at 19:48
Related: https://stackoverflow.com/questions/2759721/how-do-i-view-the-full-content-of-a-text-or-varcharmax-column-in-sql-server-20 — Thomas Weller, Dec 26 '21 at 19:49
Related: https://stackoverflow.com/questions/30867395/cant-insert-more-than-43679-character-to-sql-server-single-cell — Thomas Weller, Dec 26 '21 at 19:49
Related: https://stackoverflow.com/questions/33460390/sql-server-ntext-field-limited-to-43-679-characters — Thomas Weller, Dec 26 '21 at 19:49
Related: https://stackoverflow.com/questions/39847121/unicode-characters-get-truncated-nvarcharmax — Thomas Weller, Dec 26 '21 at 19:49

score 0 · Accepted Answer · answered Nov 03 '15 at 09:35

0

You can try to use varchar(max) to store huge amount of data. See MSDN

We recommend that you store large data by using the varchar(max), nvarchar(max), or varbinary(max) data types. To control in-row and out-of-row behavior of these data types, use the large value types out of row option.

You can also check the same issue here: SSMS - Can not paste more than 43679 characters from a column in Grid Mode

answered Nov 03 '15 at 09:35

Rahul Tripathi

168,305
31
280
331

Thanks... This is actually SSMS issue and still open with Microsoft team. – Abhash786 Nov 03 '15 at 09:46
@Abhash786:- You are welcome. Indeed but the Microsoft team has also asked to used `varchar(max)` now to store huge data and may be `text` will be deprecated soon(*although no official news about it*);) – Rahul Tripathi Nov 03 '15 at 09:47
@Thanks Rahul... I think varchar(max) can go till 8000 bytes or 2 GB... Right? – Abhash786 Nov 03 '15 at 09:51
@Abhash786:- Indeed, which is quite a big amount of data to store...right? ;) – Rahul Tripathi Nov 03 '15 at 09:55

Not able to save more than 43679 char in text datatype column in SQL Server

1 Answers1

Linked