Sql-server – 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

localesql server

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?

Best Answer

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.

Related Solutions

Sql-server – the difference between varchar and nvarchar

An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you're having to maintain, even while enjoying some of the benefits of full Unicode storage.

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Best Answer

Related Solutions

Sql-server – the difference between varchar and nvarchar

Sql-server – Select columns from result set of stored procedure

Related Topic