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.
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();
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.