Loc identifier '' of source column 'PAT_NUM_ADT' and locale identifier '1033' of destination column 'PAT_ID_OLD' do not match

Question

I get this error when I do 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 the db settings but that didn't help.

Has anyone seen this error?

Answer 1

, , , . SqlBulkCopyColumnMappings . , . SqlBulkCopyMapping Add SqlBulkCopy.ColumnMappings.Add.

. , , , . .

Answer 2

, SqlBulkCopy, , , SqlBulkCopy.

:

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder("Data Source=ServerName;User Id=userid;Password=****;Initial Catalog=Deepak; Pooling=true; Max pool size=200; Min pool size=0");

        SqlConnection con = new SqlConnection(cb.ConnectionString);

        SqlCommand cmd = new SqlCommand("select Name,Class,Section,RollNo from Student", con);

        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        SqlBulkCopy sbc = new SqlBulkCopy("Data Source=DestinationServer;User Id=destinationserveruserid;Password=******;Initial Catalog=DeepakTransfer; Pooling=true; Max pool size=200; Min pool size=0");

        sbc.DestinationTableName = "StudentTrans";


        sbc.WriteToServer(rdr);


        sbc.Close();
        rdr.Close();
        con.Close();

: "0" "RollNo" "1033" "" .

.

:

SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder("Data Source=ServerName;User Id=userid;Password=****;Initial Catalog=Deepak;");

        SqlConnection con = new SqlConnection(cb.ConnectionString);

        SqlCommand cmd = new SqlCommand("select Name,Class,Section,RollNo from Student", con);

        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();


        SqlBulkCopy sbc = new SqlBulkCopy("Data Source=DestinationServer;User Id=destinationserveruserid;Password=******;Initial Catalog=DeepakTransfer;");

        sbc.DestinationTableName = "StudentTrans";

        sbc.ColumnMappings.Add("Name", "Name");
        sbc.ColumnMappings.Add("Class", "Class");
        sbc.ColumnMappings.Add("Section", "Section");
        sbc.ColumnMappings.Add("RollNo", "RollNo");

        sbc.WriteToServer(rdr);
        sbc.Close();
        rdr.Close();
        con.Close();

.

Answer 3

. , , .

, : SELECT NULL AS ColumnName...

nullable varchar (3).

, , select : SELECT CONVERT (VARCHAR (3), NULL) AS ColumnName...

, !

Answer 4

sal

, , , , . SqlBulkCopyColumnMappings .

! , . - .. , , Locale Id - , , h ###.

Answer 5

, , VARCHAR DataTable INT.

, , . , (, , ).

, .

Answer 6

, . , , sp_help, , . ,

Answer 7

, .

Answer 8

A great way to debug is to take the SQL query used in SqlBulkCopy and run it in Management Studio as a choice, for example, change it select * from [table_name]to select * into newTable from [table_name], then look at the nullability and data types of 'newTable' compared to 'table_name'. If there are any differences, then you will probably end up with this error error. Correct the query or target table until they match, and then your team will work.