bcp collation problem?

[Glasgow]

I export data from a table to an output file using BCP. That data is later imported into other databases using BULK INSERT. This all worked fine under SQL 2000.

The table has one large field defined as varchar. That field contains delimiters that are ASCII 254 & ASCII 255. When I BCP the data in SQL 2005, the ASCII 255 characters are retained but ASCII 254 characters are translated as lower case 't'.

Is anyone able to shed any light as to why this behaviour might have changed under SQL 2005? Do I need to specifiy collation or revert to format files?

 

[Glasgow]

I confess I made a huge meal of doing so but but I appear to have resolved the problem. I now specifically reference Code Page 850 when exporting (BCP) the data and re-importing (BULK INSERT) but I'd still be interested to know why this worked in SQL 2000.

 

[MartinCroft]

It could be collation issues, are both database the same collation. In each DB run

SELECT DATABASEPROPERTYEX(db_name(),'COLLATION')