nvarchar max gives 'String or binary data would be truncated'

Question

I am trying to insert string-values larger than 5500 characters into a MSSQL 2008 database. I get the error

String or binary data would be truncated.

when I try to insert these values even though the data type of the column is declared nvarchar(max). Is there a cap on the string-size that I can enter? If so, what would be a solution to this problem?

EDIT

When i manually enter the data via mgmt studio it works. However, I'm performing the updates via JDBC-driver & prepared statements.

My query is of the form:

UPDATE table SET columnX = value1 WHERE columnX = value2;

• I add this statement to a batch, and once every 1000 statements I execute them.
• value1 in this case contains a large amount of characters.
• columnX is definitely defined as nvarchar(max)

Answer 1

Microsoft encourages us to use the SQLServerPreparedStatement to modify large-value-types like varchar(max). http://msdn.microsoft.com/en-us/library/ms378813.aspx

You could also use a stored procedure to solve the problem, that is what I did, when I had similar problems with JDBC/Pentaho.

You could write a stored procedure on the sql-server that fulfills the task. And just call that stored procedure from your Java-code.

I hope that helps!