BCP in SQL server 2005 does not truncate a column

BEHAVIOR/SYMPTOMS:

During a BCP operation in SQL 2005, a column with less precision gets truncation errors.BCP in SQL Server 2000 didn’t report truncation errors which could affect business logic in applications where precision data up to a certain decimal value is required. BCP for SQL Server 2005 reports the truncation errors and will not round off/truncate the data to be inserted which might result in existing import operations to fail which were designed using the older version of BCP utility. For Example: If the source column has 7 decimal places then the BCP output file will also contain 7 decimal places. When we import those records to a SQL 2005 database table which has only 3 decimal places, it will give the below truncation error

ERROR MESSAGE:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (62.50 rows per sec.)

CAUSE:
SQL 2000 uses ODBC 2.0 and ODBC 2.0 does not report truncation errors. So SQL 2000 BCP continued with truncation of records and imported it to the target table
SQL 2005 with SP3 uses ODBC 3.0 and it 3.0 does report truncation errors, so SQL 2005 with SP3, BCP reports a truncation error message and does not import invalid records

RESOLUTION/WORKAROUND:

• To work around this issue we have to create another table with larger data column size and use bcp to insert data to the new table. After that, use “insert into select” to insert the data to the old table.
e.g. old_table( c1 decimal(4,2)), new_table(c1 decimal(6,4)), once bcp into new_table, we can do “insert into old_table select * from new_table”. This too would round off/truncate the data as the destination table has lower precision, but this method won't cause BCP to fail.

• we can explore the options of expanding the target column or import the data with pre-defined column width. This is more preferable if destination tables could be modified to accommodate the data without losing them in transition.

STEPS TO REPRO:

1.Created a test table
create table t_testbcp (line_name varchar(100), amount decimal(15,3))

2.Data in the text file
<testbcp.txt>
Line1|1.123
Line2|1.1234
Line3|1.12345
Line4|1.123456
<testbcp.txt>

3.As we renamed the below 2 files

C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp.exe
C:\Program Files\Microsoft SQL Server\80\Tools\binn\Resources\1033\bcp.rll

4.Then ran the below command and it failed

C:\ >"C:\Program Files\Microsoft SQL Server\90\Tools\binn\bcp.exe" backup_test.dbo.t_testbcp in testbcp.txt -S Server\SQLInstance -T -t "|" -E -c

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Native Client]String data, right truncation

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 16 Average : (62.50 rows per sec.)

5. The workaround to use the switch –c or –n may not help

Note: Since the issue is related to the ODBC 3.0 driver using the old BCP did not help fix the issue in our case.

Regards,Karthic Kumaran

Support Engineer, Microsoft SQL Server.

Reviewed By,
Amit Banerjee, TL, Microsoft SQL Server