I have a table with some BIG data (up to 1MB per row) in a nvarchar(max field). I figured out how to insert these large contents into the table (they contain report formats in XML) but now when I retrieve them, they are getting about the first 4K of the column.
SQL is like this "SELECT description, bigdata FROM TABLE where ID_Key = 1
I use a vistadb connection, command and dataAdapter and do a fill of a dataset. I should be able to get the entire LOB - correct? It appears that the insert put the whole thing in correctly.
I believe John has worked this out by exporting the field to a file instead of reading it through the particular third-party control. Perhaps he can elaborate on the .NET code he used in case anyone else runs into this.
The other advice we had on his initial length issue has to do with the history of VistaDB's implementation and the way the engine handles string-valued types. He was getting a 301 error (Character column data exceeds maximum) when trying to insert a value longer than 8000 or so characters even though the table had that column declared as NVarChar(MAX). That problem had turned out to be an issue with the type of the parameter used to pass in the data from .NET... which was passed as a string value without declaring a specific type (using VistaDBCommand.Parameters.Add(string parameterName, object value)) which by default creates the input parameter with its type effectively as NVarChar(8192), not as NVarChar(MAX).
These are implemented as separate classes internally because of the extra logic for storing extended-length values in NVarChar(MAX) columns, so you have to pick whether you want regular NVarChar(n) or NVarChar(MAX). Some parts of the API will not automatically scale across to the (MAX) variant from the non-MAX type.
The solution for the 301 error on INSERT was to use a different overload of the Add method to specify an explicit type and then assign to the Value property of the VistaDBParameter returned by the Add method. (A VistaDBParameter instance can also be created explicitly first and then added in yet another overload of the Add method.) If the type is specified as VistaDBType.NVarChar (or as VistaDBType.VarChar) the length needs to be specified as -1 to indicate (MAX). The type could also be specified as VistaDBType.NText (or as VistaDBType.Text) which VistaDB implements as NVarChar(MAX) (or as VarChar(MAX)) and not as the very different SQL Server version of NText (or Text)--which are both deprecated by SQL Server.