How to fix '[Microsoft][SQL Server Native Client 10.0]String data, right truncation' Error with BCP

0
votes

I'm setting up a BCP and I receive an error each time.

What can I do to fix this problem ?

This is running in Windows Server 2008 and on SQL Server 11

Executed command : 

bcp mydb.dbo._table in \\app\data$\ID\file.CSV -f \\app\format.fmt -T -S dbname

Format file : 

8.0                         
8                           
1   SQLCHAR 0   100 ";" 1   A   SQL_Latin1_General_CP1_CI_AS  
2   SQLCHAR 0   100 ";" 2   B   SQL_Latin1_General_CP1_CI_AS  
3   SQLCHAR 0   100 ";" 3   C   SQL_Latin1_General_CP1_CI_AS  
4   SQLCHAR 0   100 ";" 4   D   SQL_Latin1_General_CP1_CI_AS  
5   SQLCHAR 0   100 ";" 5   E   SQL_Latin1_General_CP1_CI_AS  
6   SQLCHAR 0   100 ";" 6   F   SQL_Latin1_General_CP1_CI_AS  
7   SQLCHAR 0   100 ";" 7   G   SQL_Latin1_General_CP1_CI_AS  
8   SQLCHAR 0   100 "\r\n"  8   H   SQL_Latin1_General_CP1_CI_AS

Expected : The file is well generated with the right content

Actual results :

Starting copy... SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation 1000 rows sent to SQL Server. Total sent: 1000 1000 rows sent to SQL Server. Total sent: 2000 1000 rows sent to SQL Server. Total sent: 3000

1
sqlsql-serverbatch-filebcp
to get more info, add the "-e" option to your bcp command. this will output a very accurate list of some of the problematic rows. I suggest viewing this error output file and, most likely, eventually, your source file "file.csv". I suspect your input data is not as you suspect. Either delimiter trouble, fields larger than expected or something along those lines. Looking at output of file created using -e option of BCP will help the most. - jamie
Looks like the date column in the problem regarding the log file. Example of date in the CSV file : 19800706 but the column is nvarchar(8) so I don't really understand. I will check the entire file. - Musab Gosset

1 Answers

0
votes

The error was in the input file.

The first row was the headers.

Adding -F2 in the command fixed my error (To skip the first row).