I am using bcp to export and import data, for example:
bcp "exec db.dbo.procedure" queryout "C:\Users\I\Desktop\ps.dat" -c -r "*" -t; -S -U -P
bcp db.dbo.table in C:\Users\I\Desktop\ps.dat -e "C:\Users\I\Desktop\ps_error.dat" -c -r "*" -t; -m1000000 -S -U -P
If I execute these statements without -r, bcp uses the default CRLF as end of row. Later, the import fails with right data truncation.
After so many attempts I have seen that CRLF is detected as two bytes of data, and it does not fit the table format. When I use the above statements it works perfectly.
Why is this happening? Is this a bcp bug, or is the expected behaviour?
According to MS that is the expected behaviour:
https://learn.microsoft.com/en-us/sql/tools/bcp-utility
this article explains all the parameters and for this case these are the ones we are interested in:
-c
Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c is not compatible with -w.
-r
row_term Specifies the row terminator. The default is \n (newline character). Use this parameter to override the default row terminator. For more information, see Specify Field and Row Terminators (SQL Server).
So it seems that by removing -r which sets the row terminator to \n (LF) , -c is taking over and setting the row terminator to \r\n (CRLF)