SqlBulkCopy exception, find the colid

sql sqlbulkcopy

Question

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?

Accepted Answer

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:

string.Replace(";", "CHAR(59)");

After that, everything inserted smoothly.


Popular Answer

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)



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why