Data of type SQLBIT always 1?

Hello all,

Apologies if this is posted in the wrong forum, however I wasn't exactly sure as to which one would be the best for my query...

I have recently inherited the job of maintaining a database of internet logs and I am using the bcp utility to bulk insert rows into the database. The problem is that for the fields which are classified as SQLBIT in the .fmt file (and the field in the database is bit) are always inserted as '1' even if the data is actually a '0'. Any value will be inserted into the database as a 1 except for NULL which is inserted as NULL.

I tested changing the type to SQLCHAR in the .fmt file, and this allows the insert of a 0 into the database.

Does anyone have any ideas as to why this is happening? The database is SQL Server Standard Edition running on NT 4.0.

Any help/suggestions would be greatly appreciated.

Regards,
Daniel

Is your data file native/binary or character? It appears it is character, so you would use SQLCHAR and the characters '0' or '1' to represent the bit value. If your file is native (binary), then there would be a byte in it with a binary zero, 0x00, to represent a zero bit, or anything not zero to represent a 1 bit, and you'd use SQLBIT. If you use SQLBIT with a character file, since both “0” and “1” are not binary zeros, BCP treats them both as 1s. In order for SQLBIT format file work correctly, the corresponding field in the data file should contain binary 0 instead of character ‘0’.

Hope that explains it for you,

Don|||Hi Don,

yes, that makes perfect sense...thanks very much for your help! I'll insert them as a type SQLCHAR.

Cheers,
Daniel