BCP Error

[skydive]

Hi All,

I have been encountering a problem with BCP which I cannot resolve. I have read all the posts on this site relevent to BCP but have still not been able to resolve my problem. I have enclosed all relevent information.

Here is the destination table on Sql Server
CREATE TABLE [dbo].[COLIN] (
[id] [numeric](18, 0) NOT NULL ,
[name] [char] (13) NULL
) ON [PRIMARY]


Here is the input file "test.txt"
1,"Colin "
2,"Deirdre "
3,"Gavin "
4,"Kye "


Here is the format file "bcp.fmt"
7.0
2
1 SQLNUMERIC 1 19 "," 1 id
2 SQLCHAR 0 13 "\r\n" 2 name


Here is the bcp command
bcp "Northwind..COLIN" in "test.txt" -S"CIPCGAL002" -U"sa" -P"" -t"\," -r"\n" -f"c:\bcp.fmt"


And finally here is the error message:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1


If anyone can be of any assistance it would be much appreciated.
Colin

 

[Rosko]

Lose the quotes in your .txt file, I think that will do the trick. Otherwise, try to do a bcp out first, you will in which format the bcp command expects its file to be.

GL

 

[skydive]

Hey Rosko,

Thanks for that I tried the bcp out and opened the file, the data in the first column was strange looking the reason being that I had declared the first column to be exported/imported as SQLNUMERIC so it exported the number in binary. This was the reason I could not get it working initially as I was trying to bcp in a text and the format file was expecting a binary number.
So I amended my bcp.fmt file:
7.0
2
1 SQLCHAR 0 18 "," 1 id
2 SQLCHAR 0 13 "\r\n" 2 name


Thanks for your time.
Colin

******************************************************
THREAD CLOSED
*********************************************************