Thursday, June 23, 2011

[BCP] When native isn't exactly what you put in

An oddity I wanted to mention:

I BCPd out the contents of a table using QUERYOUT, in NATIVE format (/n).
I then proceeded to import the file into an identical table using
BULK INSERT WITH ( DATAFILETYPE = 'native', KEEPIDENTITY )
and used SQL Data Compare (plug!) to compare the two tables.

To my surprise, out of 1.5m rows, 21 were different. In this case, instead of a dash (0x2D), I wound up with what looks like a dash but isn't (0x96).

If anybody's heard of this, please chime in. Both were varchar(40), though the source was 2008 and the target was 2005.