BCP puts nulls in output text file

Hi,

I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

Is there a way I can tell it to just not place anything in the output for this field.

Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

Thanks for any help.
ScottWhat does your bcp command look like?

with -c it should be OK.

from
create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
insert bcp select 'asd', 'asd', 'asd', 'asd'
insert bcp select 'asd', '', '', 'asd'
insert bcp select 'asd', 'asd', 'asd', 'asd'
exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

I get
asd,asd,asd,asd
asd, , ,asd
asd,asd,asd,asd|||The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

Thanks for your help.
Scott|||However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).sql