Trying to get a large amount of data (column type is “text”) from an MS SQL Server

by

The question:

I want to get like 300MB of data (text column) out of an MS SQL Server, but with the library I’m using I’m getting an out-of-memory exception as soon as I try to access the field. Unfortunately, I’m bound to that library and cannot switch to something else.

I’ve tried getting chunks using SUBSTRING(), but that returns varchar and the max len is 8000, so getting 300MB in chunks of 8K would take forever.

Is there any other way to do this? If I could get that 300MB in 3x 100MB chunks that would be fine, 100MB don’t seem to throw an exception. Maybe to somehow split the data server-side?

MS SQL Server Version is 14.0.3436.1

Thanks in advance

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

I’ve tried getting chunks using SUBSTRING(), but that returns varchar
and the max len is 8000, so getting 300MB in chunks of 8K would take
forever.

The reason SUBSTRING returns no more than 8000 characters is because you are using the deprecated text data type. Use varchar(MAX) for large values instead, which allows larger values to be returned with the standard T-SQL SUBSTRING function.

The proper way to read data from a legacy text column in smaller chunks is with a READTEXT statement. Below is an example gleaned from the documentation, with the starting position and length parameterized (integer parameter types).

DECLARE @ptrval VARBINARY(16);  
SELECT @ptrval = TEXTPTR(pr_info)   
   FROM pub_info pr INNER JOIN publishers p  
      ON pr.pub_id = p.pub_id   
      AND p.pub_name = 'New Moon Books'  
READTEXT pub_info.pr_info @ptrval @StartPosition @Length;

Of course, you’ll need to first get the length of the value to avoid reading beyond the end of the value. I assume your SUBSTRING attempt already does this but that’s just a guess without seeing your code.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment