the locale id '0' of the source column 'PAT_NUM_ADT' and the locale id '1033' of the destination column 'PAT_ID_OLD' do not match

Question

I get this error when I do a bulk insert with select * from [table_name], and another table name:

the locale id '0' of the source column 'PAT_NUM_ADT' and the locale id '1033' of the destination column 'PAT_ID_OLD' do not match

I tried resetting my db collation but this did not help. Has anyone seen this error?

Answer 1

I would check what your default locale settings are. Also, you'll need to check the locale of both tables using sp_help to verify they are the same. If they aren't you'll need to convert it to the correct locale

Answer 2

SQL server columns do not have locale ids. You can't use sp_help to find what "locale" your table is in. The error message is itself an error. This is Microsoft's common practice of sending you on a wild goose chase when they really don't have a clue what's going on.

I've seen in other forums frantic advice to be sure that collations between databases match, and that locales from Holland should not be copied over to locales from Mexico. All hogwash.

If you are copying less than a full set of fields from one table to another, whether that table is on another domain across the world, or is collocated in the same database, you just have to select them in order. SqlBulkCopyColumnMappings do not work. Yes, I tried. I used all four possible constructors, and I used them both as SqlBulkCopyMapping objects and just by providing the same information to the Add method of SqlBulkCopy.ColumnMappings.Add.

My columns are named the same. If you're using a different name as well as a different order, you may well find that you have to actually rename the columns. Good luck.

Answer 3

The answer by sal

If you are copying less than a full set of fields from one table to another, whether that table is on another domain across the world, or is collocated in the same database, you just have to select them in order. SqlBulkCopyColumnMappings do not work.

is according to my work absolutely right! Thanks for posting it. Everything has to be the same - data types, etc. Each time it finds a mismatch it throws the mysterious Locale Id error - funny yet frustrating as h###.