SQL Server field getting truncated

Question

Ok I'm using SQL Server 2008 and have a table field of type VARCHAR(MAX). Problem is that when saving information using Hibernate, the contents of VARCHAR(MAX) field is getting truncated. I don't see any error messages on either the app server or database server.

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

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

1. Changed the database field from VARCHAR(MAX) to TEXT and same problem occurs.

2. Used the SQL Server Profiler and I noticed that the full text content is being
   received by the database server, but for some reason the profiler freezes when trying to view the SQL with the truncation problem. Like I said, just before it freezes, I
   did noticed that the full text file content (383KB) are being received, so it seems that it might be the database problem.

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

NOTE: just want to mention that I'm just going into SQL Studio and just copying the TEXT field content and pasting it to Textpad. That's how I noticed it's getting truncated.

Thanks in advance.

Answer 1

Your problem is that you think Management Studio is going to present you with all of the data. It doesn't. Go to Tools > Options > Query Results > SQL Server. If you are using Results to Grid, change "Maximum Characters Retrieved" for "Non XML data" (just note that Results to Grid will eliminate any CR/LF). If you are using Results to Text, change "Maximum number of characters displayed in each column."

You may be tempted to enter more, but the maximum you can return within 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 has issues also. First set Results To Grid > XML data to 5 MB or unlimited, then do:

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

Now this will produce a grid result where the link is actually clickable. This will open a new editor window (it won't be a query window, so won't have execute buttons, IntelliSense, etc.) with your data converted to XML. This means it will replace > with > etc. Here's a quick example:

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

Result:

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

(It does kind of have IntelliSense, validating XML format, which is why you see the squigglies.)

BACK AT THE RANCH

If you just want to sanity check and don't really want to copy all 383K elsewhere, then don't! 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 method of verification.

(I've since written a tip about this here.)