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

user_1538666 0
Viewed 66381

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.

sql-server-2008-r2 bcp sql-server-2008
10 Answers

In my case the reason was that in one field there was written "|" = chr$(124) and the separator was in my case "|" = chr$(179).

MS SQL to not make a difference between both characters. I eliminated the chr$(124) and then the import by BCP works fine.

user_113551660

After spending 4 hrs, doing a ton of trail and error, I found that the solution can be as simple as the table where you are importing the data to should have a suitable schema for the file that you trying to import. ex: In my case. I was importing a .csv with 667,aaa,bbb into a table that has a schema of int(4),char(2),char(2) causing String Data, Right Truncation.

user_78811770

My bcp was ignoring any of those newline characters like \r, \n, \r\n\, 0x0d, 0x0a, 0x0d0x0a, etc.. The only sulution I found was to include "real" newline directly into the bcp command. I think this works because the csv was generated on the same server as the bcp is running on. When I transfer the csv to the mssql server manually then 0x0a works as well inside BULK INSERT.

Please note, that nl1=^ must be followed by two new blank lines.

my_script.bat:

@echo off
setlocal enableDelayedExpansion

set nl=^


set cmd=bcp db_name.db_schema.my_table in stats.csv -w -t, -r "!nl!" -S my_server -U my_username -P password123
!cmd!
user_21100080
Related