BulkCopy error: The locale id '1025' of the source column 'CreatedBy' and the locale id '1033' of the destination column 'CreatedBy' do not match

Question

I’m performing SQL bulk copy using the SqlBulkCopy class. I created my destination table to be exactly the same of my source table. They both have the same table name, columns names, data types and even the same collation. I did also the sql mapping to ensure accuracy.

My code as follow:

SqlConnection SourceConnection =  new SqlConnection(SourceConnectionString);
SqlConnection DestinationConnection =  new SqlConnection(DestinationConnectionString);

DestinationConnection.Open();
SourceConnection.Open();

SqlCommand commandSourceData = new SqlCommand("SELECT * FROM Requisitions;", SourceConnection);

SqlDataReader reader = commandSourceData.ExecuteReader();    

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DestinationConnection))

    {
           bulkCopy.DestinationTableName = "Requisitions";


           bulkCopy.ColumnMappings.Add("RequisitionId", "RequisitionId");
           bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
           bulkCopy.ColumnMappings.Add("DateCreated", "DateCreated");
           bulkCopy.ColumnMappings.Add("AircraftTailNum", "AircraftTailNum");
           bulkCopy.ColumnMappings.Add("JobNumber", "JobNumber");
           bulkCopy.ColumnMappings.Add("ShopCode", "ShopCode");
           bulkCopy.ColumnMappings.Add("RequestedByName", "RequestedByName");
           bulkCopy.ColumnMappings.Add("RequestedById", "RequestedById");
           bulkCopy.ColumnMappings.Add("Status", "Status");
           bulkCopy.ColumnMappings.Add("IsCancelled", "IsCancelled");
           bulkCopy.ColumnMappings.Add("IsProcessed", "IsProcessed");


           try
               {
                  // Write from the source to the destination.
                  bulkCopy.WriteToServer(reader);
                }
          catch (Exception ex)
                {
                     Console.WriteLine(ex.Message);
                 }
          finally
                 {

                    reader.Close();
                  }
          }

However, every time I execute my code I got the following error:

The locale id '1025' of the source column 'CreatedBy' and the locale id '1033' of the destination column 'CreatedBy' do not match.

I have searched a lot over the internet but no luck for any solution. I would be so thankful if someone helped me out.

Dantee, please see [my answer](https://stackoverflow.com/a/50394661/577765) for a simpler solution that is better for performance and uses less memory. — Solomon Rutzky, May 17 '18 at 15:13

Satinder singh · Accepted Answer · 2012-12-10T11:26:27.747

Try like this

  public DataTable fetchValue()
    {
        SqlDataAdapter dap=new SqlDataAdapter("SELECT RequisitionId,CreatedBy,DateCreated,AircraftTailNum,JobNumber,ShopCode,RequestedByName,RequestedById,Status,IsCancelled,IsProcessed FROM Requisitions;", cn);
        DataSet ds=new();
        dap.Fill(ds);
        return ds.Tables[0];
    }

        DataTable dtgenerate=new DataTable();
        dtgenerate=fetchValue();

       cn.open();
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(n))
       {
           bulkCopy.DestinationTableName = "Requisitions";//DestionTableName
           // bulkCopy.ColumnMappings.Add("SourceColumnIndex", "DestinationColumnIndex");  
           bulkCopy.ColumnMappings.Add("RequisitionId", "RequisitionId");
           bulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy");
           bulkCopy.ColumnMappings.Add("DateCreated", "DateCreated");
           bulkCopy.ColumnMappings.Add("AircraftTailNum", "AircraftTailNum");
           bulkCopy.ColumnMappings.Add("JobNumber", "JobNumber");
           bulkCopy.ColumnMappings.Add("ShopCode", "ShopCode");
           bulkCopy.ColumnMappings.Add("RequestedByName", "RequestedByName");
           bulkCopy.ColumnMappings.Add("RequestedById", "RequestedById");
           bulkCopy.ColumnMappings.Add("Status", "Status");
           bulkCopy.ColumnMappings.Add("IsCancelled", "IsCancelled");
           bulkCopy.ColumnMappings.Add("IsProcessed", "IsProcessed");

           bulkCopy.WriteToServer(dtgenerate);
       }
       cn.close();

Thank you very very much. it worked like a charm. I really still would like to know my mistake. was the solution to use Datatable class to contain to my data then inserting it into SQL table ? — dantee_87, Dec 10 '12 at 12:00
but note that in this case you are fetching the entire result set into local memory an then you are sending it to destination. Basically the idea of bulk inserting is damaged here, at least for a half of it. — Adi, Jul 25 '15 at 05:42
@Adi Exactly. And in this case (a `SELECT` statement and not an `EXEC proc`) there is no reason to take this hit on performance and memory when simply adding the `COLLATE` keyword fixes it. Please see my [answer](https://stackoverflow.com/a/50394661/577765) for details. — Solomon Rutzky, May 17 '18 at 15:02

score 2 · Answer 2 · answered Dec 21 '12 at 14:42

The collation of the source varchar columns differ to the collation on the destination varchar columns.

Loading first to a datatable works around this, however if you are loading a large amount of data you will probably hit a System.OutOfMemoryException.

score 1 · Answer 3 · answered May 17 '18 at 15:01

While you claim that the column Collations of the source and destination tables are the same, clearly they are different, at least for this one particular column.

A much simpler approach that neither eliminates the performance benefit of passing the DataReader directly to SqlBulkCopy, nor uses any additional memory, is to simply force the Collation of that column to be the destination Collation. You do this by specifying the columns of the SELECT instead of using * (a good practice in any case) which then allows you to add the COLLATE keyword to any string columns that you get this error on. For example:

@"SELECT RequisitionId, CreatedBy COLLATE {destination_collation_name}, DateCreated,
         AircraftTailNum, JobNumber, ShopCode, RequestedByName, RequestedById,
         Status, IsCancelled, IsProcessed
 FROM    Requisitions;"

score 0 · Answer 4 · answered Dec 29 '21 at 16:37

The error typically occurs when inserting NULL values from one table to another, where the tables have different "Locale" (Collation) settings.

This is typically seen in bulk copy operations from one database to another, since the two databases might have different collation configurations.

One method of preventing this issue is to use ISNULL for all of your collatable (char, varchar or nvarchar) columns to ensure that a NULL is never entered into them.

However, your

SELECT *

Is ambiguous and should be avoided wherever possible. Likely you have some NULL columns coming through in your SELECT there, and since you do not specify which columns to select, you cannot use ISNULL to prevent NULL values from being inserted.

BulkCopy error: The locale id '1025' of the source column 'CreatedBy' and the locale id '1033' of the destination column 'CreatedBy' do not match

4 Answers4