2

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

4
  • 5
    but with the library I'm using Provide more information about your application and the technology it is using. And post the code that throws the error as well as the complete error message - all of it. Note that NO ONE should be using the TEXT datatype - that was deprecated almost 20 years ago. If you can change that, do it now. May not fix the problem but at least it prevents more work at a later time.
    – SMor
    Commented Apr 28, 2022 at 10:55
  • Is this a one time thing or you'll need to do this multiple times?...because there's a multitude of ways to pull the data out of the database (including to a CSV) so that you can then fix it up and load it back into the database in smaller fields.
    – J.D.
    Commented Apr 28, 2022 at 11:22
  • @J.D. unfortunately I need to do this multiple times, and to support multiple fields to split the data would be too much of a change for other systems that rely on that, I'll go with changing the column type to varchar(max) or varbinary(max) and pray that nothing breaks
    – BETSCH
    Commented Apr 28, 2022 at 13:22
  • 1
    I hope you did a backup before you tried to change the type...
    – Josh Part
    Commented Apr 28, 2022 at 18:54

1 Answer 1

6

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.

4
  • Generally this would work, but now there's the problem that it truncates at the first null-byte (seems like SUBSTRING is doing so as well). If there's a row with like 10 MB of data and I SELECT it completely, it works, but with SUBSTRING or READTEXT, it truncates. (Would make sense since it's text) Do you know if I can change the row type from text to varchar(max) or another type that supports that kind of use like image without destroying the data?
    – BETSCH
    Commented Apr 28, 2022 at 12:47
  • @BETSCH, by null byte, do you mean a string terminator (0x00)? Note image is also depreciated. varbinary(MAX) is the right type for binary data which you apparently have.
    – Dan Guzman
    Commented Apr 28, 2022 at 13:03
  • yes, I'm talking about 0x00, and yes, it's binary data. Alright, I tried to convert text to image and it doesn't support that anyway. It supports text to varchar(max) though so that's what I'm doing now, it has to re-create the table though wich I assume will take a long time Thank you
    – BETSCH
    Commented Apr 28, 2022 at 13:17
  • @BETSCH So do you have textual data or binary data? If the former it should be in varchar(max) or nvarchar(max) if the latter should be varbinary(max) Commented May 1, 2022 at 14:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.