0

I use this code to send a SQL request:

 SqlBulkCopy bulkCopy = new SqlBulkCopy(Connection);

 foreach (DataColumn column in dt.Columns)
 {
     bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
 }

 bulkCopy.DestinationTableName = "nsi." + classifierData.Info.TableName;
 bulkCopy.WriteToServer(dt);

and get this exception :

Received an invalid column length from the bcp client for colid

Is there any way to identify which row is causing the error?

I've tried to use this, but it doesn't work (values are always the same):

 FieldInfo currentRow = typeof(SqlBulkCopy).GetField("_currentRowLength", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);
 var currentRowNumber = currentRow.GetValue(bulkCopy);

 FieldInfo _rowsCopiedField = typeof(SqlBulkCopy).GetField("_rowsCopied", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);
 var currentRowN = _rowsCopiedField.GetValue(bulkCopy);

Please, help me anyone ...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kseniya Yudina
  • 137
  • 1
  • 11

1 Answers1

1

I believe the only way to identify the row in error from purely SqlBulkCopy is using the NotifyAfter property + SqlRowsCopied event + BatchSize = 1.

From these, you can now easily have an Index property that you increment for every successfully copied row and you will be able to find the index in error.

For example, an error is thrown and you re-try with NotifyAfter = 1

bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
bulkCopy.NotifyAfter = 1;
bulkCopy.BatchSize = 1;

Obviously, not the best performance wise solution but you will find your information.

EDIT: Answer comment

Sorry, but can I get a value of row-column with error?

I don't think so. We don't know exactly which column have an issue with the error message.

Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60