bcp: Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

Question

I have recently encountered an error while working with bcp. Here is the error.

SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation

I'm trying to unpack the data into a staging table which does not have any constraints and the datatypes are also fairly large when compared to the data. I have about 11 files from different tables being bcp'd and zipped out of which only one file when unpacking errors out. This is the command which I have been using succesfully. Very recently(when trying to make a copy of the current WH and settign up the process) I have been facing issues.

bcp.exe employee_details in employee_details.dat -n -E -S "servername" -U sa -P "Password"

I have tried changing the commands to -C -T -S which worked when I gave the format manually. This is a very big and important packet I need to load in to my WH.
I don't know if I see a format file here or not. Any help is needed.

Thanks

Cinnamon girl.

Answer 1

We also faced same issue while doing BCP and it turned out to be an issue with new line character in .dat file.

View the file in Notepad++ and click on "Show All Characters" to see the new line character.

BCP throws following error with -r "\r\n" option i.e. with below command

bcp dbo.Test in C:\Test.dat -c -t "|" -r "\r\n" -S "DBServerName" -T -E

" SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation "

BCP treat all rows in file as a single row with -r "\n" or -r "\r" option i.e. with below command

bcp dbo.Test in C:\Test.dat -c -t "|" -r "\n" -S "DBServerName" -T -E

Issue was resolved when we used the Haxadecimal value (0x0a) for New Line character in BCP command

bcp dbo.Test in C:\Test.dat -c -t "|" -r "0x0a" -S "DBServerName" -T -E

Answer 2

bcp right truncation error occurs when there is too much data that can be fitted into a single column. This can be caused by improper format files(if any being used) or delimiter. The line terminator (Windows has CRLF or '\r\n' and UNIX has '\n') can also cause this error. Example Your format file contains Windows CRLF ie, '\r\n' as the row terminator but the file contains '\n' as line endings. This would mean fitting the whole file in 1 row(rather 1 column) which leads to right truncation error.

Answer 3

For us it turned out that the file we were trying to upload was in Unicode instead of ANSI format.

There is a -N switch, but our tables didn't have any NVARCHAR data.

We just saved the file in ANSI format and it worked, but if you have NVARCHAR data or you may need to use the -N switch

See TechNet - Using Unicode Native Format to Import or Export Data

Answer 4

I was also getting the truncation message. After hours of searching forums and trying suggested solutions I finally got my load to work.

The reason for the truncation message was because I was gullible enough to think that putting the column name in the format file actually mattered. It's the preceding numeric that appears to dictate where the data gets loaded.

My input file did not have data for the third column in the table. So this is how my format file looked.

... ","    1 Cust_Name       SQL_Latin1...
... ","    2 Cust_Ref        SQL_Latin1...
... ","    3 Cust_Amount     SQL_Latin1...
... "\r\n" 4 Cust_notes   SQL_Latin1...

My input file looked like this:

Jones,ABC123,200.67,New phone 
Smith,XYZ564,10.23,New SIM 

The table looked like

Cust_Name Varchar(20)
Cust_Ref  Varchar(10)
Cust_Type Varchar(3)
Cust_amount Decimal(10,2)
Cust_Notes Varchar (50)
Cust_Tel   Varchar(15)
Cust......

I'd assumed by giving the column name in the format file that the data would go into the appropriate column on the table.

This however works as the column number is important and the column name is noise.

... ","    1 A       SQL_Latin1...
... ","    2 B       SQL_Latin1...
... ","    4 C       SQL_Latin1...
... "\r\n" 5 D       SQL_Latin1...