BCP generates 0x00 characters

Hello,
I'm using BCP with a .FMT file to generate exports of some tables.
BCP sometimes messes up the file by placing 0x00 characters (ASCII value 00)
in the export. It looks like BCP translates NULL values or empty fields like
this.
Strange enough, this doesn't happen all the time. It will happen every once
in a while (data changes all the time). When it happens, it messes up the
entire file.intGod (intGod@.discussions.microsoft.com) writes:
> I'm using BCP with a .FMT file to generate exports of some tables.
> BCP sometimes messes up the file by placing 0x00 characters (ASCII value
> 00) in the export. It looks like BCP translates NULL values or empty
> fields like this.
> Strange enough, this doesn't happen all the time. It will happen every
> once in a while (data changes all the time). When it happens, it messes
> up the entire file.
My guess the 0x00 characters are there, because they appear in the data.
How does your format file look like?
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|||"Erland Sommarskog" wrote:
> My guess the 0x00 characters are there, because they appear in the data.
> How does your format file look like?
Dear Erland,
I found the root of the problem but I don't know why BCP behaves like this.
The problem is: sometimes (only sometimes) BCP will treat an empty field
incorrectly and insert a 0x00 character instead of the string. By using a
CASE on that field (case fieldName when '' then ' ' else fieldName), the
problem is solved. Still, it's annoying.
I've never come across this problem but I've never used .fmt files before.
I've always used tab-delimited.|||intGod (intGod@.discussions.microsoft.com) writes:
> I found the root of the problem but I don't know why BCP behaves like
> this.
> The problem is: sometimes (only sometimes) BCP will treat an empty field
> incorrectly and insert a 0x00 character instead of the string. By using a
> CASE on that field (case fieldName when '' then ' ' else fieldName), the
> problem is solved. Still, it's annoying.
> I've never come across this problem but I've never used .fmt files before.
> I've always used tab-delimited.
Since you did not post the format file, I cannot comment much more. But
it appears that you use the queryout option. I have not use queryout myself
much, but I have the impression that it is considered buggy.
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