WhiteLotus (2/10/2016)
Hi Guys ,I am migrating database from server A to server B . The collation in Server A is SQL_latin1_general_CP1_CI_AS and server B is
Latin1_General_CI_AS
So the database from server A has collation SQL_latin1_general_CP1_CI_AS.
I need to consolidate them in 1 server so that’s why I migrate the database.
My question :
Will it become a problem in the future ? is it difficult to change database collation?
YES/YES
PS: I will not join database taken from server A with the existing databases in Server B
Any feedback are much appreciated
Thank you
The tempdb does also have a collation, the collation of the tempdb can only be the same to one of the databases you have got. Although there are 'workarounds' in general differences in collation orders are a pain in the a??.
To my knowledge it is not easy to change the collation of a database.
I did this once, because of a install with the 'wrong' collation.
It took me a few days to create a (fairly) generic script(s) to solve this problem.
And it took some hours process all tables.
Global way I did go about this.
1. Create a script which will restore relations/indexes and some more.
2. Strip the database of relations/indexes and some more.
3. Change the collation of each (character) column of each table.
4. Recreate relatons/indexes and some more.
I never have understood why MS does not deliver a change collation function/help/procedure.
Could be that in the 'newer' versions of SQL-server there is something for this.
Please keep us informed, what you decided and how you managed this problem.
Ben