When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.

Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...

There are a few possible reasons for this, but the most likely is that you have the text in row option set on for the table. This forces SQL Server to save the data inline, meaning that the length of the data for all columns in the row, including the ntext field, has to be under roughly 8K. Use this code to set it off:

sp_tableoption N'MyTable', 'text in row', 'OFF'

Check out BOL for the ntext data type, and pay attention to the text in row information for the subtleties of this feature..

It is also possible that somewhere along the line the data is being passed using a variable that has a smaller capacity than you think. If the text in row possiblity doesn't bear any fruit, post the code you're using to store the data, from the app through T-SQL.

Don 

This did not have any effect.

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.

 Thanks

Donkiely's solution is incorrect.

Text in row does not do what you've claimed.  It does not force the text into the row, it only allows it to be in the row if it will fit (or it forces it out even with it set to store in row).

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

Motley:

Donkiely's solution is incorrect.

Text in row does not do what you've claimed.  It does not force the text into the row, it only allows it to be in the row if it will fit (or it forces it out even with it set to store in row).

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

D'oh. You're right. I forgot that it just stores the data outside of the row if it doesn't fit, even if you've specified it.

So, once again, we need to see the code as I've asked for. Even if you're inserting the data in SSMS, how exactly are you doing it? And what is the table strucutre?

Don 

Philotech:

I am making the changes directly in SSMS, so the application has nothing to do with the problem.

Well, SSMS doesn't allow you to make changes to or insert data larger than 4,000 characters into a ntext column.

As for your application, please see my original post above.