bcp, empty strings and null

Not sure if this is the right section...
I've got a problem with empty strings in a table turning into null (ascii
code 0x00) values when bcp'ed out to file:
When I bcp out of a table columns that have an empty string in them get
written to file as a null value (ascii code 0x00).
For example:
--SQL
use Sandpit_SL
create table bcpTest (Col1 varchar(10), Col2 varchar(10), Col3 varchar(10))
insert bcpTest select 'COLUMN1', '', 'COLUMN2'
And then bcp:
bcp Sandpit_SL.dbo.bcpTest out C:\bcpTest.csv -S DevDB1 -U sa -t "," -c
Produces an output file where the 2nd field contains ASCII (0x00) rather
than an empty string.
How can I make it so that the second field is an empty string? Am I missing
the point here?
Thanks
StephenSte (Ste@.discussions.microsoft.com) writes:
> I've got a problem with empty strings in a table turning into null (ascii
> code 0x00) values when bcp'ed out to file:
> When I bcp out of a table columns that have an empty string in them get
> written to file as a null value (ascii code 0x00).
> For example:
> --SQL
> use Sandpit_SL
> create table bcpTest (Col1 varchar(10), Col2 varchar(10), Col3
> varchar(10))
> insert bcpTest select 'COLUMN1', '', 'COLUMN2'
> And then bcp:
> bcp Sandpit_SL.dbo.bcpTest out C:\bcpTest.csv -S DevDB1 -U sa -t "," -c
> Produces an output file where the 2nd field contains ASCII (0x00) rather
> than an empty string.
I agree that behaviour is not what you would expect, so I submitted a
bug for it on
http://lab.msdn.microsoft.com/Produ...px?feedbackId=F
DBK50454
However, as I note in the bug report, I don't really expect a fix. I think
I can see the logic here.
If you import the file, the NUL character will become the empty string.
Now, consider the file:
COLUMN1,,COLUMN2
What do you get if you import this file? Answer: you get a NULL value in
Col2. Which neither that may be what you want.
The basic problem is that character format does not have a way to expess
NULL values. Sybase in their original design circumvented this, by
leaving out empty strings from the mix (That is, '' was the same as ' '.)
What is the consumer in the other end for this file?
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 (esquel@.sommarskog.se) writes:
> I agree that behaviour is not what you would expect, so I submitted a
> bug for it on
> [url]http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=F[/ur
l]
> DBK50454
> However, as I note in the bug report, I don't really expect a fix. I think
> I can see the logic here.
Indeed, the bug was quickly closed as "By design".
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

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