SQL Server can't store more then 4000 characters in a local string variable

Question

I tried to use text or ntext as the variable's type but they are disallowed.
Is there a way to bypass this?

Answer 1

(n)varchar(max) will help you

Answer 2

For anything not MSSQL 2005 or MSSQL 2008:

---

You can get 8000 in a varchar(8000)

Another option is to use a temporary table and populate that with the information you need. Use an autoindex to maintain order.

Answer 3

TEXT and NTEXT are being deprecated in a future version of SQL Server.

Instead, you should be looking to use VARCHAR(MAX) and NVARCHAR(MAX) which remove the previous 8000 and 4000 size limits respectively, taking you up to 2^31-1 bytes.