SqlBulkCopy with different collations

.net sqlbulkcopy sql-server

Question

I need to migrate data from one DB to another. I choosed to use SqlBulkCopy, but have a problem with it, because source database has different collation than destination, so, I've got an exception:

System.InvalidOperationException: The locale id '1049' of the source column 'Id' and the locale id '1033' of the destination column 'Id' do not match.
   at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
   at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
   at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
   at MigrateToNormalized.DirectMapCommand.Migrate(SqlConnection source, SqlConnection destination, SqlTransaction transaction) in D:\Projects\APS\DTE\MigrateTo
Normalized\MigrateToNormalized\MigrateToNormalized\DirectMapCommand.cs:line 53
   at MigrateToNormalized.Program.Main(String[] args) in D:\Projects\APS\DTE\MigrateToNormalized\MigrateToNormalized\MigrateToNormalized\Program.cs:line 32

Can anyone tell me, how to resolve this issue without direct usage of COLLATE statements in SQL query? Is there some easy way to change collation for all columns in source database?

Popular Answer

It is right that when we use SqlBulkCopy, some time it gives error, the best way to map the columns when you are using SqlBulkCopy.

My Privious Code :

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

The Code Was giving me the Error as : The locale id '0' of the source column 'RollNo' and the locale id '1033' of the destination column 'Section' do not match.

Now After Column Mapping my Code Is Running Successfully.

My Modified Code is :

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

This code is running Successfully.

Try This and enjoy.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why