"epaetz" <ep******@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Simon Hayes wrote: Which version of MSSQL? What data type is the column? Is ANSI_PADDING
on or off for the column? If your column is char(1), then this would
be expected, as char columns are padded out with spaces; a varchar
should not be padded, though.
If this doesn't help, I suggest you post (simplified) CREATE TABLE
and INSERT statements to show exactly what your table and data look like.
Simon
SQL Server 2000
Varchar 16
ANSI_PADDING is off.
Give it a shot. Create a table with three columns, all varchar. Insert
an empty string into each to get rid of any nulls. Then do a bcp out to
an output file and let me know if you get the same results.
Eric
I used this test script (on 8.00.760 Enterprise):
set ansi_padding off
go
create table eric (col1 varchar(16) null)
go
insert into eric select '' -- empty string
insert into eric select ' ' -- single space
go
select col1, len(col1) as 'Length', datalength(col1) as 'Datalength', col1 +
'X'
from eric
go
Then I exported the file with bcp:
bcp Development..eric out c:\temp\eric.txt -S kilkenny -c -T
When I checked eric.txt with a hex editor, it showed this:
00 0D 0A 20 0D 0A
So the empty string is an ASCII NUL character in this case, but the space is
ASCII 20. Is this the behaviour you see, or do you get something different?
Setting ANSI_PADDING ON didn't change the output (and BOL recommends it
should always be on anyway).
Simon