bcp error - Invalid column type from bcp client for colid 1 - Solution

You might be experiencing the following error with bcp when loading text data to SQL Server table;

It is possible to see this error when there is a data type mismatch between a column related to the destination table and a field coming from the file. However, there can be a situation where you have used a view for the bcp and underline table has been changed. If so, you may see the same error.

In my case, my destination table has an identity column, hence I created a view for getting data via bcp. I started seeing this error after making some modifications to the table. Once the view is dropped and recreated, it worked without any issue.

SQL Server bcp error - String data, right truncation

We generally expect a proper row terminator with files loaded using bcp and with all accurately defined switches, data should be loaded without any issue. But sometime, you may see issues like below even if you try with switches like -r "\r\n" or -r "\n".

In most cases, the reason is the row terminator. If you open the file to be loaded using something like Notepad++, and enable Show All Characters (icon is in the toolbox), you should see the exact row terminator.

If you see something like above, then you need to add the row terminator as a hexadecimal value. In this case, if I add -r "0x0a", then my data loading will work without any issue.

SQL Server bcp Error - Unexpected EOF file encountered in BCP file

Here is another issue I experienced today with bcp utility (see my previous post related to this: SQL Server bcp Error - Invalid ordinal for field in xml format). The file I tried imported was UNIX-based file and bcp did not identify the proper row-terminator;

I was sure about the row-terminator but this did not work even with BULK INSERT. As usual, did a search, then found a suggestion to use hexadecimal value for row-terminator instead of \n.

And it worked.

You may experience the same, try this and see whether it solves the issue.

SQL Server bcp Error - Invalid ordinal for field in xml format

We still use bcp utility for importing or exporting data, specifically when we have to work with large amount of data (I have written post on this, comparing different methods for loading data, read it for more info: How to load/insert large amount of data efficiently). In order to load data from a file, need to instruct the utility with the structure of the file and mapping, and instruction can be passed either specifying details with switches along with bcp command or adding a format file to the command.

If you need to use a format file, it needs to be generated first. We can use same bcp command for generating it.

bcp tempdb.dbo.Customer format nul -T -S DINESH-PC\SQL2014 -c -t, -x -f "E:\Temp\Customer_c.xml"

for more information on bcp command, read this.

Sometime it does not work as we expect. You may experience the following error when you try to generate the file;

SQLState = HY000, NativeError = 0

Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid ordinal for field 3 in xml format file.

If experience it, the main reason could be Column names with spaces. Check and see whether the table used for generating the xml file has column names with spaces. If so, you will surely get this error.

Here is an example. As you see, if you try to execute the above bcp command against this table, you will get this error.

USE tempdb;
GO

IF OBJECT_ID('dbo.Customer') IS NOT NULL
 DROP TABLE dbo.Customer;
GO
CREATE TABLE dbo.Customer
(
 CustomerId int PRIMARY KEY
 , [Customer Code] char(5) NOT NULL
 , [Customer Name] varchar(50) NOT NULL
 , [Credit Limit] money NOT NULL
);
GO

If you remove spaces from columns, there will be no errors and file will be generated.

How to load/insert large amount of data efficiently

Importing data from other sources is a very common operation with most of database solutions as not all data cannot be entered row-by-row. When large amount of data needs to be imported, we always consider about the constraints added to the table and minimal logging expecting improved performance.

SQL Server supports a set of tools for importing data: Import and Export Wizard, bcp (Bulk Copy Program), BULK INSERT, OPENROWSET (BULK). Preference always goes to either bcp or BULK INSERT, however, to improve the performance of it, some of the options have to be considered and set. This post explains how to load a large amount of data using bcp with various options.

For testing the best way, I created a database called TestDatabase and a table called dbo.Sales. I prepared a text file called Sales.txt that has 7,301,921 records matching with dbo.Sales table structure. The Recovery Model of the database is initial set as Full.

Then I loaded the table with following bcp commands. Note that each and every command was run after truncating and shrinking the log file.

--Loding without any specific option
bcp TestDatabase.dbo.Sales in C:\Temp\Sales.txt -S DINESH-PC\SQL2014,8527 -T -c

--Loding with TABLOACK option for forcing to lock the entire table
bcp TestDatabase.dbo.Sales in C:\Temp\Sales.txt -S DINESH-PC\SQL2014,8527 -T -c -h "TABLOCK"

--Loding with our own batch size
bcp TestDatabase.dbo.Sales in C:\Temp\Sales.txt -S DINESH-PC\SQL2014,8527 -T -c -b 3000000 

--Loding with our own batch size and TABLOCK 
bcp TestDatabase.dbo.Sales in C:\Temp\Sales.txt -S DINESH-PC\SQL2014,8527 -T -c -b 3000000 -h "TABLOCK"

Once the table is loaded four times, then the table was loaded again with Bulk Logged recovery model. All commands were executed just like previous one, making sure that log is truncated and shrunk before the execution.

Finally here is the result.

Command	Recovery Model	Time (ms)	Log file size after loading
With no specific option	Full	189,968	3,164,032 KB
With TABLOCK	Full	275,500	1,475,904 KB
With batchsize	Full	108,500	2,377,088 KB
With batchsize and TABLOCK	Full	99,609	1,964,480 KB
With no specific option	Bulk Logged	140,422	3,164,032 KB
With TABLOCK	Bulk Logged	239,938	26,816 KB
With batchsize	Bulk Logged	121,828	2,377,088 KB
With batchsize and TABLOCK	Bulk Logged	86,422	1,475,904 KB

As you see, you can get improved performance in terms of the speed by setting the batch size and tablock with bulk-logged recovery model. If you really consider about log file growth, then tablock option with bulk-logged recovery model is the best.

I believe that BULK INSERT offers the same result, however it is yet to be tested in similar way.