Received an invalid column length from the bcp client for colid 6

Question

I want to bulk upload csv file data to sql server 2005 from c  code but I am encountering the below error -      Received an invalid column length from the bcp client for colid 6    when bulk copy write to database server

User · Answer

I got this error message with a much more recent ssis version  vs 2015 enterprise  i think it s ssis 2016    I will comment here because this is the first reference that comes up when you google this error message   I think it happens mostly with character columns when the source character size is larger than the target character size    I got this message when I was using an ado net input to ms sql from a teradata database    Funny because the prior oledb writes to ms sql handled all the character conversion perfectly with no coding overrides   The colid number and the a corresponding Destination Input column   you sometimes get with the colid message are worthless   It s not the column when you count down from the top of the mapping or anything like that  If I were microsoft  I d be embarrased to give an error message that looks like it s pointing at the problem column when it isn t   I found the problem colid by making an educated guess and then changing the input to the mapping to  Ignore  and then rerun and see if the message went away   In my case and in my environment I fixed it by substr   ing the Teradata input to the character size of the ms sql declaration for the output column    Check and make sure your input substr propagates through all you data conversions and mappings    In my case it didn t and I had to delete all my Data Conversion s and Mappings and start over again  Again funny that OLEDB just handled it and ADO net threw the error and had to have all this intervention to make it work   In general you should use OLEDB when your target is MS Sql

User · Answer

I just stumbled upon this and using  b stil s snippet  I was able to figure the culprit column  And on futher investigation  I figured i needed to trim the column just like  Liji Chandran suggested but I was using IExcelDataReader and I couldn t figure out an easy way to validate and trim each of my 160 columns   Then I stumbled upon this class   ValidatingDataReader  class from CSVReader   Interesting thing about this class is that it gives you the source and destination columns data length  the culprit row and even the column value that s causing the error   All I did was just trim all  nvarchar  varchar  char and nchar  columns   I just changed my GetValue method to this    object IDataRecord GetValue int i                object columnValue   reader GetValue i            if  i  gt  -1  amp  amp  i  lt  lookup Length                        DataRow columnDef   lookup i               if                                                      string columnDef  DataTypeName       varchar                          string columnDef  DataTypeName       nvarchar                          string columnDef  DataTypeName       char                          string columnDef  DataTypeName       nchar                     amp  amp                                        columnValue    null  amp  amp                      columnValue    DBNull Value                                                               string stringValue   columnValue ToString   Trim                     columnValue   stringValue                    if  stringValue Length  gt   int columnDef  ColumnSize                                          string message                            Column value       stringValue Replace                                                  with length     stringValue Length ToString        0                               from source column      this as IDataRecord  GetName i                              in record     currentRecord ToString        0                               does not fit in destination column     columnDef  ColumnName                               with length       int columnDef  ColumnSize    ToString        0                               in table     tableName                             in database     databaseName                             on server     serverName                             if  ColumnException    null                                                throw new Exception message                                             else                                               ColumnExceptionEventArgs args   new ColumnExceptionEventArgs                             args DataTypeName    string columnDef  DataTypeName                            args DataType   Type GetType  string columnDef  DataType                             args Value   columnValue                          args SourceIndex   i                          args SourceColumn   reader GetName i                           args DestIndex    int columnDef  ColumnOrdinal                            args DestColumn    string columnDef  ColumnName                            args ColumnSize    int columnDef  ColumnSize                            args RecordIndex   currentRecord                          args TableName   tableName                          args DatabaseName   databaseName                          args ServerName   serverName                          args Message   message                           ColumnException args                            columnValue   args Value                                                                              return columnValue          Hope this helps someone

User · Answer

I know this post is old but I ran into this same issue and finally figured out a solution to determine which column was causing the problem and report it back as needed  I determined that colid returned in the SqlException is not zero based so you need to subtract 1 from it to get the value   After that it is used as the index of the  sortedColumnMappings ArrayList of the SqlBulkCopy instance not the index of the column mappings that were added to the SqlBulkCopy instance   One thing to note is that SqlBulkCopy will stop on the first error received so this may not be the only issue but at least helps to figure it out   try       bulkCopy WriteToServer importTable       sqlTran Commit          catch  SqlException ex        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           throw new DataFormatException String Format  Column   0  contains data with a length greater than   1    column  length               throw

User · Answer

Great piece of code  thanks for sharing   I ended up using reflection to get the actual DataMemberName to throw back to a client on an error  I m using bulk save in a WCF service    Hopefully someone else will find how I did it useful    x000D   x000D  static string GetDataMemberName string colName  object t    x000D    foreach PropertyInfo propertyInfo in t GetType   GetProperties      x000D      if  propertyInfo CanRead    x000D        if  propertyInfo Name    colName    x000D          var attributes   propertyInfo GetCustomAttributes typeof DataMemberAttribute   false  FirstOrDefault   as DataMemberAttribute  x000D          if  attributes    null  amp  amp   string IsNullOrEmpty attributes Name   x000D            return attributes Name  x000D          return colName  x000D          x000D        x000D      x000D    return colName  x000D    x000D   x000D   x000D

User · Answer

One of the data columns in the excel  Column Id 6  has one or more cell data that exceed the datacolumn datatype length in the database   Verify the data in excel  Also verify the data in the excel for its format to be in compliance with the database table schema   To avoid this  try exceeding the data-length of the string datatype in the database table   Hope this helps

User · Answer

Check the size of the columns in the table you are doing bulk insert copy  the varchar or other string columns might needs to be extended or the value your are inserting needs to be trim  Column order also should be same as in table   e g  Increase size of varchar column 30 to 50     ALTER TABLE  dbo   TableName  ALTER COLUMN  ColumnName  Varchar 50

User · Answer

I faced a similar kind of issue while passing a string to Database table using  SQL BulkCopy option  The string i was passing was of 3 characters whereas the destination column length was varchar 20   I tried trimming the string before inserting into DB using Trim   function to check if the issue was due to any space  leading and trailing  in the string  After trimming the string  it worked fine   You can try text Trim

[c#] Received an invalid column length from the bcp client for colid 6

Examples related to c#

Examples related to .net

Examples related to sql-server

Examples related to database

Examples related to sql-server-2005