#@ Row 1, Column 7: String data, right truncation @#

I am trying to BCP a ton of data files into a SQL 2005 database. The first row of data in one of my files looks like:

1000|100000156752|100000176409|100000000000|100000000000|9.4|M|9.4||1/22/1993||1||||100|||||||||1|1/22/1993|||||||

The error file has this:

#@. Row 1, Column 7: String data, right truncation @.#
0 0 0 0 0 .00 17|27.7|M|27.7||2/2/1993||1||||100|||||||||1|2/2/1993|||||||

I've built a format file based on the SQL Table definitions with this command:
FOR %%f IN (*.*) DO bcp IRIS.dbo.%%f format nul -T -n -t"|" -r"\n" -f%%f.format

The format file for lines 1-8 look like:
1 SQLNUMERIC 1 19 "|" 1 SITE_ID ""
2 SQLNUMERIC 1 19 "|" 2 WEL_ID ""
3 SQLNUMERIC 1 19 "|" 3 WPOO_ID ""
4 SQLNUMERIC 1 19 "|" 4 WSMP_ID ""
5 SQLNUMERIC 1 19 "|" 5 CC_ID ""
6 SQLNUMERIC 1 19 "|" 6 CORE_LENGTH ""
7 SQLCHAR 2 2 "|" 7 LENGTH_MEASM_UNIT_ID SQL_Latin1_General_CP1_CI_AS
8 SQLNUMERIC 1 19 "|" 8 LENGTH_OF_CORE_RECOVD ""

Here is the DOS command window results for this file:
F:\Data>cd import

F:\Data\Import>FOR %f IN (CONVT_CORES.*) DO bcp IRIS.dbo.%f in %f -e..\BCP_Error
\%f.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\%f.format

F:\Data\Import>bcp IRIS.dbo.CONVT_CORES in CONVT_CORES -e..\BCP_Error\CONVT_CORE
S.error -Slocalhost -Usa -Psol3admin -f..\BCP_Format\CONVT_CORES.format

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

BCP copy in failed

F:\Data\Import>cd..

So, what is wrong?

Seems, that the data types do not make, make sure that the data types in the destination tables are capable to import the data, otherwsie truncation will take place and will chop the data.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||All datat types are made larger than needed by the largest data piece being imported. It works quite easily in SSIS but BCP gives me this error. With a few hundred tables, SSIS will take me days to setup and execute each one.

Something is wrong with BCP in SQL 2005!!!|||

Hi Guys,

Make sure the table schema on the target server matches the one on the source server. This will solve the proble. There is nothing wrong in the BCP in SQL 2005.

Roshan.

|||

Try using sqlchar across the board. If you are exporting from a SQL server and then importing the data back in, use native format to export, and specify this in your format file.

I ran into this problem myself a few weeks ago.

|||Exporting from Oracle. I've even gone so far as to make all columns varchar(xx) to see if that would fix it. It does *not* fix it. I've tried various formats for the CSV and they all give me this error. I exported to XML, wrote a small app to import that and I have problems with one, and only one, table out of 736 tables. I cannot say BCP works as I cannot get it to work. SSIS works on all but 7 tables so I know the data can go in. <bah type="humbug" />|||I have bcp jobs that run every night from production to staging, and one started failing after putting SP2 on SQL server. It was only on one table. I finally fixed the problem by deleting the table and recreating it from a script extracted from production. All I can figure is that something in the table schema got corrupted.