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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply