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 -