Change ntext to nvarchar(max) in a live database

I have a live SQL 2005 database that has ntext fields, when the ntext fields go over 4000 chars the record can no longer be edited. It throws a string or binary data would be truncated error. I tried turning text in row OFF, but it did not work. Can anyone forsee any problems with changing the ntext fields to nvarchar(max) in the live database? Also, I came across sp_tableoption N'MyTable', 'large value types out of row', 'ON', does this work for ntext also? sp_tableoption N'MyTable', 'text in row', 'OFF' did not do anything.

Any help would be appreciated.

Have you set TEXTSIZE option for the connection? Check this using:

DBCC USEROPTIONS

|||

Where and how do I do this? Also I changed the ntext fields in the database to NVarChar(MAX), so now I can modify the values in the database directly, but when I do it through the application and the VarChar(MAX) fields are over 4000 it does not save the changes, but does not return an error either. It returns as if the stored procedure executed successfully.

|||

I'm not sure why you felt the need to create yet another thread, but the answer remains the same (and the 3rd time I'm giving it to you):

The problem is more likely that the parameters are being declared as either the wrong type, or you are not declaring the type at all, and letting it default. Make sure your ntext parameters are declared as such.

|||

Actually it was a problem upgrading a SQL 2000 database to SQL 2005 then modifying it, I had to recreate the table. Once I recreated the table and did a Select Into, the problem was resolved.

And I created another thread because I wanted to change the datatypes from ntext to nvarchar(max) in a live database and wanted to know if it was going to have any ill effects. In my experience, when you ask a different question in an existing thread, you do not get an answer for both questions, so I wanted to keep them separated.