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 " " option i.e. with below command

bcp dbo.Test in C:Test.dat -c -t "|" -r "
" -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 " " or -r " " option i.e. with below command

bcp dbo.Test in C:Test.dat -c -t "|" -r "
" -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