0

This is the BCP command I use for inserting data in SQL server:

bcp sfnav.dbo.Customer in "C:\Users\TSL\Desktop\TSL Data\ID_Customer_151124.csv" -F2 -c -t "^" -r "\n" -S ftpserver\sqlexpress -U abc -P xyz

When I try to run the same command in a batch file it results in an error:

call bcp sfnav.dbo.Customer in "C:\Users\TSL\Desktop\TSL Data\ID_Customer_151124.csv" -F2 -c -t "^" -r "\n" -S ftpserver\sqlexpress -U abc -P xyz

Error:

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

SFDC shaikh
  • 101
  • 1
  • 1
  • 8
  • Your row delimiter is likely incorrect and should probably be `\r\n`. Check your data file in a viewer that shows special characters. Eg in Notepad++, go to View>Show Symbol>Show All Characters and inspect the row delimiter. – TT. Feb 17 '16 at 09:50
  • Similar issue reported [here](http://stackoverflow.com/q/11566658/243373) on StackOverflow – TT. Feb 17 '16 at 09:54
  • 1
    If the row delimiter is really `\n`, try the hexadecimal value: `-r "0x0a"` – TT. Feb 17 '16 at 09:57
  • I tried to replace \n by "0x0a" but still it is showing same error. Normaly it works fine when I run the command seperately. But when I run in batch that is .bat file it results in Error – SFDC shaikh Feb 17 '16 at 10:29
  • Did you check the actual delimiters in Notepad++ or a similar program clearly showing the row delimiter? – TT. Feb 17 '16 at 10:36
  • Well, like I said in my first comment, your delimiter should be `\r\n`. – TT. Feb 17 '16 at 10:40
  • then what should be the solution. User is sending me file with that format. How should I modify my command to execute? – SFDC shaikh Feb 17 '16 at 10:46
  • 2
    Did I not just type out twice what I think your problem is? Ok for a third time, use the switch `-r \r\n` instead of `-r \n`. – TT. Feb 17 '16 at 11:01
  • Its working for me Thanks for the help – SFDC shaikh Feb 17 '16 at 11:53
  • Try using Hexadecimal EOL character as explained here http://stackoverflow.com/a/27793486/813739 – Ali Apr 04 '17 at 06:20

2 Answers2

1

You want to check length of the receiving table columns. If it is shorter than the value from BCP for this column you will have fun having this error!!!

Rem Sadikov
  • 41
  • 5
1

I was getting RIGHT TRUNCATION error when I tried extracting an image file from Access and inserting it as a blob into SQL Server database. I solved my problem by changing the blob column data type from VARBINARY(MAX) to IMAGE on the target Sql server. If this Image data type change works for you then just keep in mind that the max size is 2 GB.

dcarmack
  • 11
  • 1