Using SqlBulkCopy and getting this exception:
Received an invalid column length from the bcp client for colid 30.
I've been banging my head against this one for hours. I know what row is having the issue, but I don't know which column "colid 30" is. There are 178 columns in the data table. All values seem to be correct and I don't see any that are longer than any column data types in my database.
This database holds property listings and currently has over 3 million records, all of which are just fine.
Is there a way to pinpoint what colid 30 is? Or is there a way to view the actual SQL that the bcp is submitting to the database?
I hope this helps solve someone else's issues as well.
The error was because one of the string/varchar fields in the datatable had a semicolon ";" in it's value. Apparently you need to manually escape these before doing the insert!
I did a loop through all rows/columns and did:
After that, everything inserted smoothly.
Check the size of the columns in the table you are doing bulk insert. the varchar or other string columns might need to be extended.
e.g, Increase size 30 to 50 =>
ALTER TABLE [dbo].[TableName] ALTER COLUMN [ColumnName] Varchar(50)