I have a table with a varchar(max) column to store a large configuration text string (it really a xml file but I need to have in a table).
Working in .Net I use an SQLCommand object to insert the data.
A varchar(max) field is supposed to take 2GB of data, but when my data exceeds about 65000 characters, the field remains blank.
The key colums are inserted, no error is detected, the varcahr(max) column just remains blank.
If I truncate my data string to 65000 it works fine, but 70000 does not (I have not tried 65535 :-))
Server is 2008R2 Express