Size limit problem with ntext in Sql Server CE 3.5 and Visual Studio

Question

I am experiencing some strange behavior with SQL Server CE 3.5 SP2.

I have a table with 2 columns; one of type int named ID, which is the primary key, and one of type ntext named 'Value'. The 'Value' column is supposed to contain rather long string values. However, when I try to store a string that is longer than 4000 characters, the value turns into an empty string!

I am using the Visual Studio 2010 server explorer to do this.

What's going on? I thought this 4000 character limit was for nvarchar and that ntext had a 2GB limit. Am I forgetting something or are these limits different for SQL Server CE? MSDN is not very clear on this.

Answer 1

According to the documentation the limit is 536,870,911 characters:

http://msdn.microsoft.com/en-us/library/ms172424(v=SQL.100).aspx

This seems to explain what you're seeing:

http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/9fe8e826-7c20-466c-8140-4d3b0649ac09

Answer 2

Alright, after trying a lot of things and reading many obscure posts on the subject, it turned out to be not a sql server CE problem at all, but an issue with Visual Studio.

There is a setting under Options->Database Tools->Query results, that specifies the maximum numbers of characters retrieved from a query. What happened was that after the string was entered in the Server Explorer table editor, it was actually persisted in SQL Server CE but visual studio could not display it due to the aforementioned setting.