SQL2005 (Data Migration)

Today I try to setup a new SQL2005 server which will be used to replace the development server of SQL2008. Orginial I think I can simply detect the SQL2008 server and then use the "attach" method to utilize those database files from SQL2008 to SQL2005. However, the process fails due to reason of compatibility.

At the end I require to do the following for each database:

1) Generate script from the orginial SQL 2008 Server (include schema of Tables, SP, Views,etc)

2) Create database in the SQL2005 Server and then execute those scripts in Query Window of SSMS for those database

3) Use SSMS export function to copy records from SQL2008 to SQL2005.

However, while I try to do step 3 I get the below error message which cause me fail to do the data migration. Have try to change "regional setting" ot the two servers and also, change collation of the two SQL servers but still fail.

[...More than one code page (1252 and 936)...]

It is because the collation used in the orginial SQL2008 server is "SQL_Latin1_General_CP1_C1_AS" but the collation of the new SQL2005 server is "Chinese_PRC_C1_A1", the problem seems can't be solved by changing collation of those database of the SQL2008 server.

Finally I find that the solution is to change the collation of the new database of SQL2005 server to "SQL_Latin1_General_CP1_C1_AS" while the database is created.