Search code examples
sqlsql-serverbatch-filebcp

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

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

Solution

  • 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).