0

The purpose is that I want to change collation (database, table, column) from Chinese_Taiwan_Stroke_CS_AS to SQL_Latin1_General_CP1_CI_AS.

There is the way that I change the collation on database.

First, I use the old_db tasks --> General scripts... and set the script collation false with schema only.

Then, I renew the scripts database name to new_db and set the collation = SQL_Latin1_General_CP1_CI_AS.

There is no error to here.

Finally, I want to import the data from old_db to the new_db by export data from old_db to destination new_db, but it will show the error 0xc02020f4.

I think that this error is related to collation issue, but I want to change collation so the old_db and new_db must be different. How can I solve it?

By the way I have viewed other topics about change collation but it's still not work for me due to the error:

Incorrect syntax near the keyword 'CONVERT'

so I try another way to do.

Dale K
  • 25,246
  • 15
  • 42
  • 71
KENNY CHEN
  • 1
  • 2
  • 1
    Does this help? https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0fbdd156-af6d-4d65-a63f-f507e0aab420/sql-server-import-error-0xc02020f4?forum=ssdt are you transferring data by CSV? Please edit your question and describe how you are transferring the data – Nick.Mc Jan 26 '22 at 05:55
  • Hello Nick, thank you for your feedback. I transferred the data using tasks --> export data. The data source is SQL server native client old_db and the destination is also the SQL server native client new_db. The only different between old_db and new_db is the collation. – KENNY CHEN Jan 26 '22 at 07:23

1 Answers1

0

Changing the collation at any level has no effect on actual data stored. You need to move externally or internally the data from a column to another or a table to another or all the table of the database to another database after allways having precisely specified the correct collation in the ALTER or CREATE statement

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • I think if I just create database with correct collation on new_db, then the old_db can export to new_db successfully. However, I need to reset all the foreign key and primary key. Not sure if it's the root cause? – KENNY CHEN Jan 27 '22 at 01:34