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