SQL Server field becomes truncated

Question

Good. I am using SQL Server 2008 and have a table field of type VARCHAR(MAX) . The problem is that when saving information using Hibernate contents of the VARCHAR(MAX) field become truncated. I do not see error messages on either the application server or the database server.

The content of this field is just a text file. The size of this text file is 383 KB.

This is what I have done so far to fix this problem:

• The database field has been VARCHAR(MAX) from VARCHAR(MAX) to TEXT and the same problem occurs.

• I used Profiler SQL Server, and I noticed that the full text of the content is in the database received by the server, but for some reason the profiler freezes when trying to view SQL with a truncation problem. As I said, shortly before it freezes, I noticed that the full contents of the text file (383 KB) were received, so it seems that this may be a problem with the database.

Has anyone encountered this problem before? Any ideas what causes this truncation?

NOTE. I just want to mention that I just go into SQL Studio and just copy the contents of the TEXT field and paste it into Textpad. This is how I noticed that it was truncated.

Thanks in advance.

Answer 1

Your problem is that you think Management Studio will present you all the data. This is not true. Go to Tools> Options> Query Results> SQL Server. If you use "Results for the grid", change the "Maximum characters received" to "Non XML data" (just note that "Results for the grid" will exclude any CR / LF). If you are using Results for Text, change the Maximum Number of Characters Displayed in Each Column.

You may be tempted to enter more, but the maximum that you can return to Management Studio is:

 65535 for Results to Grid 8192 for Results to Text 

If you really want to see all the data in Management Studio, you can try converting it to XML, but this also has problems. First set the results to grid> XML data up to 5 MB or no limit, then do:

 SELECT CONVERT(XML, column) FROM dbo.table WHERE... 

Now this will result in a grid result where the link is really clickable. This will open a new editor window (it will not be a query window, so you will not have launch buttons, IntelliSense, etc.) with your data converted to XML. This means that it will replace > with > etc. Here is a quick example:

 SELECT CONVERT(XML, 'bob > sally'); 

Result:

When you click on the grid, you get this new window:

(It has IntelliSense validating the XML format, so you see squigglies.)

BACK FROM

If you just want to check your sanity and don't want to copy all 383K elsewhere, then don't do it! Just check using:

 SELECT DATALENGTH(column) FROM dbo.table WHERE... 

This should show you that your data was captured by the database, and the problem is the tool and your verification method.

(Since then I wrote a review about this here .)

Answer 2

try using SELECT * FROM dbo.table for XML PATH