Search code examples
sql-server-2012smo

What is the max length that BitConverter.ToString(byte[]) can handle from a SQL SMO query?

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?

Solution

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