Error on bcp

Hi again

Sometimes, in some files .dat, thee following error occurs:

#@. Row 1, Column 8: Invalid character value for cast specification @.#
3877 597 2.0 Manual 1C15H4 <NULL> 24590.0000 1 2 G 2.0 ATIVO US$ 2006 2006 2 <NULL>

But the columns are OK.

Somebody can help me?The error indicates that some value in the line could not be converted to the data type of the destination column. It can be due to the format of the specified value. If you post a repro script then it might be easier to tell what the problem might be.|||Hi,

Sorry.. Only today I saw your response.

So, the dat file that I use is something like this:

1;ABADIA;12*
2;ABADIA DOS DOURADOS;18*
3;ABADIANIA;16*
4;ABAETE;18*
5;ABAETETUBA;21*
6;ABAIARA;13*
7;ABAIBA;18*
8;ABAIRA;12*
9;ABARE;12*
10;ABATIA;25*
11;ABELARDO LUZ;31*
12;ABOBORA;12*
13;ABRANTES;12*
14;ABRE CAMPO;18*
15;ABREU E LIMA;23*
16;ABUNA;28*
17;ACAIACA;18*
18;ACAILANDIA;17*
19;ACAJUTIBA;12*
20;ACARA;21*

and the command line that I use is:

bcp cdiretodev5.dbo.cidade in "./loader/dat/cidade.dat" -U sa -P pwd123 -S BART\SQLEXPRESS -c -t; -r * -E -e d:\cidade.log

The table is:

table name: cidade

columns:

id_cidade numeric(10)
no_cidade varchar(50)
id_estado numeric(4)

Thanks!!!!|||Your row terminator seems incorrect. Do you have CR/LF after *? If so, you need to change the -r option to -r "*\n" or -r "*\r\n". See the Books Online topic on BCP utility for details on the options, format files etc.|||Hi,

thank you for your help.. Only now I could see.. sorry...
well, I put the option with -r "*\n", but and some lines, The bcp doesn′t generate the *, like follows:

155;237;SLX;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;007001-7;;*
156;237;SDX;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;007001-7;;*
1317;511;3.0 E;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;021069-2;;*
1318;511;3.0 SL;;;R$;;1;5;;1;;;;;;;2;;;2 ou 4;0;021070-6;;*
1988;46;Sedan Wind 1.0;5C19Z1;SIMP;R$;18486.0000;1;5;;1;Powertech 1.0L M.P.F.I. 4cil.;4056 mm;46 l;8,3 mkgf a 3.000 rpm;14.8 km/l;11.7 km/l;1;2000;2001;4;0;

Is there a limit for number of columns to line?

The new error now is #@. Row 1, Column 27: String data, right truncation @.#

thank you very much.|||The error message indicates that the data in column 27 or row 1 exceeds length of the destination column. So check the length of data against your table's column definition. There is a limit to the number of columns in a table in SQL Server but there is really none in the data file. As long as you have to terminators defined properly BCP should be able to parse the file correctly assuming it is formatted correctly.