21

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.

cinnamon girl
  • 211
  • 1
  • 2
  • 3
  • Can you be a lot more specific please? What does "fairly large" mean? Can you show the actual CREATE TABLE for the table definition, and a few samples of longer lines from the file? – Aaron Bertrand Jul 19 '12 at 18:12
  • Well Aaron, Sorry for not being clear enough.My file can be between some 1000 KB to 800,000KB depending on the traffic or peak hour. My table has sample columns as epoch_time(bigint), server_id(varchar),uid_rl(int),apl(int) which are identity columns and some other columns of which distributed between int and float. Thanks Cinnamon girl – cinnamon girl Jul 19 '12 at 19:42
  • I'm not asking to see the whole file, or vague things like `varchar` - `varchar(what)`? What does the input look like? You're giving us a vague error message that indicates you're trying to stuff at least one value that's too big for its column, but you won't tell us the table definitions or show us the data that causes it? How do you expect us to help? Maybe you could print out your file, put it on a dartboard, and throw a dart at it, and it will show you the value that's causing the problem? That's what you're asking us to do for you. – Aaron Bertrand Jul 19 '12 at 19:44
  • Do you have to use `bcp`? Can you try Import and Export Data (64-bit) (aka, the DTS wizard)? You must meticulously specify the data format for that just like SSIS and you have to use raw data types instead of SQL data types, but it does work very well and allows you to preview how the data will be interpreted. – Bacon Bits May 22 '14 at 18:57
  • Make sure the schemas/columns match. You may even consider dropping your target table and recreating it with the script from the source table. – jumxozizi Nov 30 '16 at 12:31

11 Answers11

24

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.

File with LineFeed 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
Mayank Jha
  • 371
  • 1
  • 4
  • 10
4

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.

Sourav
  • 365
  • 2
  • 7
  • 17
  • I don't really understand your analysis. However, +1 for the line feed mention. For my case, we had line feed characters in the data, which BCP really does not like. – Richard Aug 28 '14 at 16:51
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...
zarak
  • 2,933
  • 3
  • 23
  • 29
Joe
  • 41
  • 1
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

Charles Byrne
  • 834
  • 2
  • 11
  • 20
  • Good one! I managed to import using the csv file saved as ANSI...however still getting some truncation errors at some point of the file... also I can't remove the double quote encapsulation while using BCP.... any ideas? – Paulo Henrique Jun 02 '17 at 19:13
  • @PauloHenrique Sorry I don't have a solution. You could possible change the delimiter then handle the field in a staging table. Lately I've just been loading the data into a .Net DataSet then passing it as a User Defined Table Type to a stored proc,but that doesn't lend itself to much flexibility. I also end up sequencing the the type name when the type changes. It is more involved but easier to troubleshoot. For adhoc stuff I just make an XML string and then parse that in a stored proc, but sometimes hard to troubleshoot if there is a lot of data transformation. – Charles Byrne Jun 06 '17 at 12:37
2

I know this is old - but I just came across an instance where I was getting this error, turns out one of my numeric fields had more decimals that was allowed by the schema.

Chris
  • 1,118
  • 8
  • 24
2

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.

Ivan Aracki
  • 4,861
  • 11
  • 59
  • 73
Albert Unger
  • 21
  • 1
0

Open the files in notepad++. GO to View tab->show symbols->show all characters. I was also facing the same issue in .tsv files.one tab was misplaced.

Jitendra Rathor
  • 130
  • 7
0

Late, but still: In my case I exactly got this one

SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation

And the problem was that the schema changed. The target database had two new fields. Once I installed the previous schema, the import succeeded.

Marcel
  • 15,039
  • 20
  • 92
  • 150
0

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.

Chandu Rajasagi
  • 11
  • 1
0

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!
kafe
  • 157
  • 8
0

I was getting this error too when trying to execute the following code in a shell script:

bcp dbo.GLbalances in ./input/CostCenters.csv -S ServerName -E -d DatabaseName -T -c -t "," -F 2 -e ./output/errors_CostCenters.csv

The problem was the target table specified was incorrect (a copy/paste error) so the target table's structure was not compatible with the data in the .csv file. It worked properly when it was updated to:

bcp dbo.CostCenters in ./input/CostCenters.csv -S ServerName -E -d DatabaseName -T -c -t "," -F 2 -e ./output/errors_CostCenters.csv
knot22
  • 2,648
  • 5
  • 31
  • 51