Post
Ask Question

Received Invalid Column Length from bcp Client for Volid N

When you try to insert the rows from a DataTable and the data in one of the columns of one of the rows is too big to fit into the destination column in the database, you get a SqlException with the error message: "Received an invalid column length from the bcp client for colid N." (Where "N" is a number.) It doesn't tell you which row, and it's a pain to figure out what column to look at.

Exception: "Received invalid column length from bcp client for colid N".

Cause: Data is not in the proper format (for example, column length exceed the length of the column in the database).

This exception gives the column number and that is not easy to find when the Excel sheet contains a large number of columns. So we are using the following code to find the column name.

  1. protected string GetBulkCopyColumnException(Exception ex, SqlBulkCopy bulkcopy)  
  2.   
  3. {  
  4.       string message = string.Empty;  
  5.       if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))  
  6.   
  7.    {  
  8.       string pattern = @"\d+";  
  9.       Match match = Regex.Match(ex.Message.ToString(), pattern);  
  10.       var index = Convert.ToInt32(match.Value) - 1;  
  11.      
  12.       FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);  
  13.       var sortedColumns = fi.GetValue(bulkcopy);  
  14.       var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);  
  15.   
  16.    FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);  
  17.         var metadata = itemdata.GetValue(items[index]);  
  18.       var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);  
  19.         var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);  
  20.         message = (String.Format("Column: {0} contains data with a length greater than: {1}", column, length));  
  21.    }  
  22.      return message;  
  23.  }  

The preceding method returns a message with a column name and length. We will use this method when using sqlbulkcopy with a catch block as below:

  1. SqlConnection conn = new SqlConnection(Connection_Enc.Decrypt_Connect());  
  2.   
  3. try {  
  4.    conn.Open();  
  5.    //For Transfer a datatable data to Temp Table  
  6.    using (SqlBulkCopy sbc = new SqlBulkCopy(conn)) {  
  7.       sbc.DestinationTableName = "CallCenter_ZoneWiseTemp";  
  8.       sbc.WriteToServer(dtLocalData);  
  9.       sbc.Close();  
  10.    }  
  11. }  
  12. catch (Exception ex) {  
  13.    string errorMessage =string.Empty;  
  14.   
  15.    if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))  
  16.    {  
  17.      // this method gives message with column name with length.  
  18.    errorMessage = base.GetBulkCopyColumnException(ex, sbc);  
  19.      // errorMessage contains "Column: "XYZ" contains data with a length greater than: 20", column, length  
  20.      Exception exInvlidColumn = new Exception(errorMessage,ex);  
  21.    base.LogDataAccessException(exInvlidColumn, System.Reflection.MethodBase.GetCurrentMethod().Name);  
  22.  }