Search code examples
javasqlstringsql-server-2008nvarchar

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

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)
Solution

  • 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!