1

I am pulling data from a SQL Server 2012 table into a SQL SMO table and then looping through the result set. I am receiving an out of memory error at runtime when the following executes:

let data = dr["Data"] == DBNull.Value ? "NULL" : "convert(varbinary(max), '0x" + BitConverter.ToString((byte[])dr["Data"]).Replace("-", "") + "')"

It doesn't throw the error until it hits a row that has a DATALENGTH([Data]) of ~15MB (15127052 bytes).

Is there a max size of binary data that BitConverter.ToString(byte[]) can handle?

Wayne E. Pfeffer
  • 245
  • 1
  • 7
  • 15
  • Does BitConverter have a similar issue with reading data from a 15MB text file? (Just trying to flesh out if this really involves the database side of things in any way.) – Aaron Bertrand Feb 01 '17 at 19:53
  • I don't know, I haven't tried it. – Wayne E. Pfeffer Feb 01 '17 at 20:00
  • I suggested it because it might be that your question is relevant to a much wider audience (like on Stack Overflow) than just us database nuts. Which may mean faster or better answers. It would be useful to isolate it, I think, so that you can know for sure whether it is an issue with SMO or a general issue with `BitConverter` or `ToString()`. – Aaron Bertrand Feb 01 '17 at 20:11
  • I just tried it with an ~22MB text file and it was able to be opened, read, and converted. Code: byte[] buffer; FileStream fs = File.Open("C:\\Users\\wayne.pfeffer\\Downloads\\TextFile.txt", FileMode.Open); buffer = new byte[fs.Length]; fs.Read(buffer, 0, (int) fs.Length); string s = BitConverter.ToString(buffer); – Wayne E. Pfeffer Feb 01 '17 at 20:18

1 Answers1

1

It actually has absolutely nothing to do with SMO and the database and everything to do with an apparent limitation to the string.Replace() function. I broke out the LINQ code so that the data was manipulated step by step and found that when I tried to perform the s.Replace("-", ""), that's where it would error. After digging around on Stack Exchange a little I found: Does string.Replace(string, string) create additional strings? Applying what I found there, I changed the code from using string to using StringBuilder and then used the StringBuilder.Replace(string, string) function and it works. Apparently because string.Replace(string, string) creates copies of the source string, there must be some kind of memory limitation, but because StringBuilder operates on the original string instead of copy, that limitation doesn't apply.

In the end the code ends up looking something like:

StringBuilder sb = new StringBuilder(dr["Data"] == DBNull.Value ? "NULL" : BitConverter.ToString((byte[])dr["Data"]));

sb.Replace("-", "");

Thank you for your help on this.

Community
  • 1
  • 1
Wayne E. Pfeffer
  • 245
  • 1
  • 7
  • 15