Hi all,

I'm trying to change an existing SQL NText column to nvcharmax(max), and encountering an error on the size limit. There's a large amount of existing data, some of which is more than the 8k limit, I believe.

We're looking to convert this, so that the field is searchable in LINQ.

The 2x SQL statements I've tried are:

update Table
set dataNVarChar = convert(nvarchar(max), dataNtext)
where dataNtext is not null

update Table
set dataNVarChar = cast(dataNtext as nvarchar(max))
where dataNtext is not null

And the error I get is:

Cannot create a row of size 8086 which is greater than the allowable maximum row size of 8060.

This is using SQL Server 2008.

Any help appreciated, Thanks.

Update / Solution:

The marked answer below is correct, and SQL 2008 can change the column to the correct data type in my situation, and there are no dramas with the LINQ-utilising application we use on top of it:

alter table [TBL] alter column [COL] nvarchar(max)

I've also been advised to follow it up with:

update [TBL] set [COL] = [COL]

Which completes the conversion by moving the data from the lob structure to the table (if the length in less than 8k), which improves performance / keeps things proper.