BulkInsertOrUpdateAsync: Received Invalid Column Length from bcp Client for colid

[FastCoder]

So I was running the following code:

await _internalDb.BulkInsertOrUpdateAsync(results, bulkConfig, (progress) =>
                {
                    log.BulkUpsertProgress(progress);
                });

And I got the following exception: Received an invalid column length from the bcp client for colid 4

Now I am trying to determine what column is causing this problem. So after researching, I came across this site: Received Invalid Column Length from bcp Client for Volid N

Now I wrapped the above code into a try catch block:

try
            {
                await _internalDb.BulkInsertOrUpdateAsync(results, bulkConfig, (progress) =>
                {
                    log.BulkUpsertProgress(progress);
                });
            }
            catch (Exception ex)
            {
                string message = string.Empty;
                if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))

                {
                    string pattern = @"\d+";
                    Match match = Regex.Match(ex.Message.ToString(), pattern);
                    var index = Convert.ToInt32(match.Value) - 1;

                    FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
                    var sortedColumns = fi.GetValue(bulkcopy);
                    var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

                    FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
                    var metadata = itemdata.GetValue(items[index]);
                    var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                    var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
                    message = (String.Format("Column: {0} contains data with a length greater than: {1}", column, length));
                }
                return message;
            }

Now, where I am stuck at this point is how to replace the BulkCopy. So what I would like to do is to clearly define the exception and find out what column/value is causing the problem. Any help is greatly appreciated.

Thanks in advance

 

[Skydiver]

Moving to EntityFramework. It looks like you are using some kind of extension method to do the bulk insert or update. You'll have to look at the documentation of that extension method to see how to get more details out of it when an exception is thrown. The code that you got from that site was for the using ADO.NET where you have direct access to the SqlBlockCopy object. I don't know how that extension method exposes it, if it even exposes it.

 

[jmcilhinney]

Given that it says "colid 4", I would assume that that is referring to either the fourth or fifth column in the table, depending on whether the first ID is 1 or 0. I would assume that that error message means that one of your text (maybe binary, but probably text) values is too long for the column it's being inserted into. It shouldn't be too hard for you to find out what the maximum length of each text column is and then run a loop over your data to see whether any value is too long for the corresponding column.