BCP question

I am outputting table data to a file using bcp. The table consists of all CHAR datatypes, so that the output will be fixed length. The problem is that occationally a line will wrap on me. it isn't a line-lenght issue as far as I can tell, and the wrapping is occuring in the middle of the line.

My other fils come out without a hitch.

Anyone have any ideas?

TIAIt's most likely an embedded carriage return/line feed (char(13)+char(10) or either one of the above.) In order to avoid this situation you need to either fix the data with an update by replacing char(13)+char(10) with a space (' ') or create a view that selects fields individually and passes the suspected field to a function that returns the corrected value by replacing those characters with a space.|||Funny, I was just playing with that, and it turned out to be the case. No idea how the CR/LF got in there, as I actually typed the data into the VARCHAR field myself, using Enterprise Manager.

Oh, well. Without these little mysteries, how would I ever support my ulcers?|||In my case I ended up going with the view solution. What are your plans?|||The table I am reading from simply holds parameters for SAP files. It is a small table that will be maintained manually, so I will just control the input.|||I am running bcp from within a stored procedure, so I suppose I could use REPLACE on the parameter values to exclude and CR/LF values (just to be safe).

BCP Issue w/Right Truncation

Hello All,

I am attempting a bulk load of fixed position flat file data via bcp
and I have noticed that I get a Right Truncation error when trying to
load a row where the last column value is NULL.

For example:

Flat file row:
0000016M

FMT file:
7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 1 "\r\n" 3 HEIGHT

In this row, the height info is null and I get a right truncation
error. The row below, with height info goes in fine:

Flat file row:
0000016M510

Let me know what I am doing wrong!

Thanks in advancehharry (paulquigley@.nyc.com) writes:
> I am attempting a bulk load of fixed position flat file data via bcp
> and I have noticed that I get a Right Truncation error when trying to
> load a row where the last column value is NULL.
> For example:
> Flat file row:
> 0000016M
> FMT file:
> 7.0
> 3
> 1 SQLCHAR 0 7 "" 1 RECORD_KEY
> 2 SQLCHAR 0 1 "" 2 SEX
> 3 SQLCHAR 0 1 "\r\n" 3 HEIGHT
> In this row, the height info is null and I get a right truncation
> error. The row below, with height info goes in fine:
> Flat file row:
> 0000016M510

It would have help if you had posted a CREATE TABLE statement for your
table, and also provided a sample file where the is a row that fails.

But maybe you should try this file instead:

7.0
3
1 SQLCHAR 0 7 "" 1 RECORD_KEY
2 SQLCHAR 0 1 "" 2 SEX
3 SQLCHAR 0 3 "" 3 HEIGHT
4 SQLCHAR 0 0 "\n" 0

(I believe that \r\n or \n are the same here.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

bcp error: Truncation cannot occur for BCP output file

Hi,

I'm trying to export sql table as fixed length text file with format file but I got the following error message:

---

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Warning: Server data (61 bytes) exceeds host-file field length (60 bytes) for field (4).
Use prefix length, termination string, or a larger host-file field size.
Truncation cannot occur for BCP output file

---

All fields in the SQL input table have char data type and I use the format file like this:

---

7.0
29
1 SQLCHAR 0 10 "" 1 SEQ
2 SQLCHAR 0 1 "" 2 NPARSED
3 SQLCHAR 0 115 "" 3 COMPANY
4 SQLCHAR 0 60 "" 4 ADDR1
....
27 SQLCHAR 0 1 "" 27 LACS
28 SQLCHAR 0 2 "" 28 DPV
29 SQLCHAR 0 2 "\r\n" 29 ZIP4CODE

---

I've been researched about this error but I couldn't find the clear answer.

The strange thing is that all the records are char() fields, not varchar()

And I checked the max length record for the 4th column(ADDR1) and it was 60, not 61.

However I'm still getting the error.

The output file was exported but some of the records have short length.

Is this some kind of bcp bug?

I used SQL Server 2000 Standard w/ SP4

And the following is the command that I used:

---

declare @.cmd varchar(2000)

SET @.cmd = 'bcp "Input_table" out "D:\AddressUpdate\Tmp\xFixADDR.dat" -fD:\AddressUpdate\Tmp\xFixADDR.fmt -Usa -Psapass -SMyMachine''
print(@.cmd)
EXEC master..xp_cmdshell @.cmd

---

Please let me know if anyone solve the similar problem.

Thanks,

- Hyung -

Hyung,

Are the client and server code pages different. Translation of data from one code page to another results in larger size for the destination data than the original size. This is specially common in east-asian languages like Chinese, Japanese etc.

Also, what server/client versions are you using? The error seems to come from bcp executable which comes with SQL Server 2000, if you have SQL Server 2005 make sure you use the SQL Server 2005 version of bcp executable. You can check that by using bcp /v command, where version should be 09.XX.XXXX

Thanks

Waseem

|||

Thank you for your answer.

I changed the server collation by rebuilding the master database upon your advice and it solved the problem.

The SQL server version that I'm using is a kind of mixed.

I'd installed SQL Server 2000 personal edition (default) on my machine and I also installed SQL Server 2005 Developer edition on the same machine as Named instance.

It made me prevent using SQL 2000 version of Enterprise Manager with error something like "MMC was created by a later version"

And the program was created from SQL project in the Visual Studio 2005 standard edition and one IS package file is used inside the program.

Also one of the "Execute SQL Task" in the pacakge uses bcp command on the SQL Server 2000 machines.

Thank you again,

- Hyung -

bcp error: Truncation cannot occur for BCP output file

Hi,

I'm trying to export sql table as fixed length text file with format file but I got the following error message:

---

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Warning: Server data (61 bytes) exceeds host-file field length (60 bytes) for field (4).
Use prefix length, termination string, or a larger host-file field size.
Truncation cannot occur for BCP output file

---

All fields in the SQL input table have char data type and I use the format file like this:

---

7.0
29
1 SQLCHAR 0 10 "" 1 SEQ
2 SQLCHAR 0 1 "" 2 NPARSED
3 SQLCHAR 0 115 "" 3 COMPANY
4 SQLCHAR 0 60 "" 4 ADDR1
....
27 SQLCHAR 0 1 "" 27 LACS
28 SQLCHAR 0 2 "" 28 DPV
29 SQLCHAR 0 2 "\r\n" 29 ZIP4CODE

---

I've been researched about this error but I couldn't find the clear answer.

The strange thing is that all the records are char() fields, not varchar()

And I checked the max length record for the 4th column(ADDR1) and it was 60, not 61.

However I'm still getting the error.

The output file was exported but some of the records have short length.

Is this some kind of bcp bug?

I used SQL Server 2000 Standard w/ SP4

And the following is the command that I used:

---

declare @.cmd varchar(2000)

SET @.cmd = 'bcp "Input_table" out "D:\AddressUpdate\Tmp\xFixADDR.dat" -fD:\AddressUpdate\Tmp\xFixADDR.fmt -Usa -Psapass -SMyMachine''
print(@.cmd)
EXEC master..xp_cmdshell @.cmd

---

Please let me know if anyone solve the similar problem.

Thanks,

- Hyung -

Hyung,

Are the client and server code pages different. Translation of data from one code page to another results in larger size for the destination data than the original size. This is specially common in east-asian languages like Chinese, Japanese etc.

Also, what server/client versions are you using? The error seems to come from bcp executable which comes with SQL Server 2000, if you have SQL Server 2005 make sure you use the SQL Server 2005 version of bcp executable. You can check that by using bcp /v command, where version should be 09.XX.XXXX

Thanks

Waseem

|||

Thank you for your answer.

I changed the server collation by rebuilding the master database upon your advice and it solved the problem.

The SQL server version that I'm using is a kind of mixed.

I'd installed SQL Server 2000 personal edition (default) on my machine and I also installed SQL Server 2005 Developer edition on the same machine as Named instance.

It made me prevent using SQL 2000 version of Enterprise Manager with error something like "MMC was created by a later version"

And the program was created from SQL project in the Visual Studio 2005 standard edition and one IS package file is used inside the program.

Also one of the "Execute SQL Task" in the pacakge uses bcp command on the SQL Server 2000 machines.

Thank you again,

- Hyung -

BCP copies only Integer data

I'm using BCP to copy data from a fixed length delimited file into a
SQL table, i'm using a format file, here is the format file:
8.0
14
1 SQLINT 0 4 "" 1 numero_llamada ""
2 SQLNCHAR 0 1 "" 2 operario
SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 1 "" 3 cabina
SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 21 "" 4 numero_marcado
SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 11 "" 5 destino
SQL_Latin1_General_CP1_CI_AS
6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
""
7 SQLNCHAR 0 1 "" 7 minuto
SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 1 "" 8 hora
SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 1 "" 9 centesima_segundo
SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 1 "" 10 segundos
SQL_Latin1_General_CP1_CI_AS
11 SQLINT 0 4 "" 11 duracion_segundos
""
12 SQLINT 0 4 "" 12 costo_centavos
""
13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 0 3 "" 14 fin
SQL_Latin1_General_CP1_CI_AS
now my problem is that the INT and SMALLINT get into the table where
they should be, but the character fields just show "NULL" and some
fields just show squares and the last character on that string, any
ideas?
Thank you(kibagami23@.gmail.com) writes:
> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
...
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
Is the file a binary file or a text file? Since the integers make it,
I assume that it is a binary file.
Really what a fixed-length delimitted file is I don't know. Judging
from your format file, your file is fixed-width only.
One possibility that the character column has length-specifiers. In
such case you should specify the length of these specifiers in the
third column in the BCP file.
For more substantial input, and less speculation, please post CREATE TABLE
statement for the table, and a sample input file. (preferably enclosed
in a zip archive as attachment.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for the reply, yes the file is Fixed width, and i am putting
the length of the specifiers in the third column of the format file,
the file is a binary file created from a thrid party software developed
in Borland C Builder , i'll post what you ask for later today, any
other ideas in the meantime?
Thank you|||I suggest a row terminator on your last column (column 14). Rather than "",
specify "\r\n".
Is bcp importing all the rows? (i.e. Input file has 1000 records and your
table has 1000 rows.)
Hope that helps,
Joe
"kibagami23@.gmail.com" wrote:

> I'm using BCP to copy data from a fixed length delimited file into a
> SQL table, i'm using a format file, here is the format file:
> 8.0
> 14
> 1 SQLINT 0 4 "" 1 numero_llamada ""
> 2 SQLNCHAR 0 1 "" 2 operario
> SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 1 "" 3 cabina
> SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 21 "" 4 numero_marcado
> SQL_Latin1_General_CP1_CI_AS
> 5 SQLNCHAR 0 11 "" 5 destino
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLSMALLINT 0 2 "" 6 tarifa_aplicada
> ""
> 7 SQLNCHAR 0 1 "" 7 minuto
> SQL_Latin1_General_CP1_CI_AS
> 8 SQLNCHAR 0 1 "" 8 hora
> SQL_Latin1_General_CP1_CI_AS
> 9 SQLNCHAR 0 1 "" 9 centesima_segundo
> SQL_Latin1_General_CP1_CI_AS
> 10 SQLNCHAR 0 1 "" 10 segundos
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLINT 0 4 "" 11 duracion_segundos
> ""
> 12 SQLINT 0 4 "" 12 costo_centavos
> ""
> 13 SQLNCHAR 0 1"" 13 Indicador_llamada_borrada
> SQL_Latin1_General_CP1_CI_AS
> 14 SQLNCHAR 0 3 "" 14 fin
> SQL_Latin1_General_CP1_CI_AS
> now my problem is that the INT and SMALLINT get into the table where
> they should be, but the character fields just show "NULL" and some
> fields just show squares and the last character on that string, any
> ideas?
> Thank you
>|||SSd2ZSBhbHJlYWR5IHRyaWVkIHB1dHRpbmcgdGhl
IHJvdyB0ZXJtaW5hdG9yIGFuZCBpdCBrZWVw
cyBkb2luZyB0aGUKc2FtZSwgYW5kIHllcyB0aGUg
YmNwIGltcG9ydHMgYWxsIG9mIHRoZSByb3dz
LCBidXQgaW4gdGhlIHNhbWUgd2F5IG9ubHkKdGhl
IGludGVnZXIgZmllbGRzIHNob3cgb24gdGhl
IHNxbCB0YWJsZSB0aGUgY2hhcmFjdGVyIGZpZWxk
cyBzaG93cwpOVUxMIGFuZCBzb21lIG90aGVy
cyBzaG93IHNxdWFyZXMgd2l0aCB0aGUgbGFzdCBj
aGFyYWN0ZXIgb2YgdGhhdApmaWVsZHMgc2hv
d2luZywgbGlrZSB0aGlzOgoKNDQ0NDIgTlVMTCBO
VUxMCuOQsOOYtOOQtuOkseOcs+OMsDUJ5JWD
5ZWM5IWMUgk0CU5VTEwJTlVMTAlOVUxMCU5VTEwJ
ODQJNzAwCU5VTEwJTlVMTAo0NDQ0MwlOVUxM
CU5VTEwJ44C246C545i1MQnkvYzkhYNMCTUJTlVM
TAlOVUxMCU5VTEwJTlVMTAkzNAkyMDAJTlVM
TAlOVUxMCjQ0NDQ0CU5VTEwJTlVMTAnjgLbjkLnj
hLEzCeS9jOSFg0wJNQlOVUxMCU5VTEwJTlVM
TAlOVUxMCTE0MTE3CTUxMjAwCU5VTEwJPwo0NDQ0
NQlOVUxMCU5VTEwJ46C245Sw45C3MgnkvYzk
hYNMCTUJTlVMTAlOVUxMCU5VTEwJTlVMTAkyNgky
MDAJTlVMTAlOVUxMCjQ0NDQ2CU5VTEwJTlVM
TAnjoLbjpLbjgLY1CeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTI0NTg2CTc2ODAwCU5V
TEwJPwo0NDQ0NwlOVUxMCU5VTEwJ46C245S246Sw
MwnkvYzkhYNMCTUJTlVMTAlOVUxMCU5VTEwJ
TlVMTAk4NjI4NgkxNzkyMDAJTlVMTAk/ CjQ0NDQ4CU5VTEwJTlVMTAnjiLbjpLLjhLM5CeS9
jOSF
g0wJNQlOVUxMCU5VTEwJTlVMTAlOVUxMCTg1MDcJ
NTEyMDAJTlVMTAk/CjQ0NDQ5CU5VTEwJTlVM
TAnjoLbjoLLjkLkxCeS9jOSFg0wJNQlOVUxMCU5V
TEwJTlVMTAlOVUxMCTcyMDQJNTEyMDAJTlVM
TAk/ CjQ0NDUwCU5VTEwJTlVMTAnjkLDjmLTjkLbjoLLj
pLPjnLM5CeSVg+WVjOSFjFIJNAlOVUxM
CU5VTEwJTlVMTAlOVUxMCTU4OTYJMTAyNDAwCU5V
TEwJPwoKVGhhbmsgeW91Cg==|||I forgot to say that the last sample is the select from the destination
sql table|||For some reason I can't see your entire thread, but my first suggestion is
for you to view the file with a hex editor to see whether the file contains
what you expect it to.
A very odd thing is that your format file indicates that you have
1-character
SQLNCHAR columns called minuto, hora, segundos, and centesima_segundos.
How can these be represented with a single character? It seems much
more likely to me that these would be represented as unsigned 1-byte
integers
(SQLTINYINT) or other numeric data type.
Things to check with the hex editor are: is the string data actually stored
as Unicode? Is the string data terminated with char(0), or does it
contain non-printable characters (<= 001F Unicode)? (This could
cause problems, I think.)
Posting the CREATE TABLE statement for the destination table would
help.
Steve Kass
Drew University
Kibagami23 wrote:

>I forgot to say that the last sample is the select from the destination
>sql table
>
>