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.

Grassland

A suggestion that has nothing to do with my answer: stop using Profiler. Use a server-side trace instead. – Graben

@AaronBertrand Could you please explain why you advise against Profiler ? – Frogfish

@Frogfish Because Profiler can bring your server to its knees. So can a trace and so can Extended Events, but it's much easier to do with Profiler. From this article: SQL Server Profiler is a tool to be avoided on busy production servers, as shown by the tenfold increase in duration and significant reduction in throughput for the replay. – Graben

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.)

Graben

Yes, you are correct. I had the default results to Grid. Yeah I realized this after I had to write a dummy program to read from database and write that field to a file. Thank you so much, first time I encountered this problem. – Grassland

SELECT CONVERT(XML, 'bob & sally') fails with XML parsing: line 1, character 6, illegal name character. You could use SELECT 'bob & sally' FOR XML PATH('') but that gives bob & sally. I prefer SELECT 'bob & sally' AS [processing-instruction(x)] FOR XML PATH('') to avoid the entitisation. – Unifilar

@Martin good point, when I use XML this way it's usually a shortcut for moving much more basic strings to the top pane. – Graben

The only thing I'd add to this, is that changing the limit on text output requires a restart of SSMS. – Discountenance

Answer 2

try using SELECT * FROM dbo.table for XML PATH

Carsick

Answer 3

I had a similar situation. I have an excel sheet. A couple of columns in the sheet may have more than 255 chars, sometimes even 500. A simple way was to sort the rows of data, placing the rows with the most characters up on top. You actually need just one row. When SQL imports the data, it recognizes the field being more than 255 characters and imports the entire data :)

Otherwise, they suggested using regedit to change a specific value. Didn't want to do that.

Hope this helps

Parse