Collation issue

Question

Post reply

Collation issue

sqlfriend

SSC Guru

Points: 52781
More actions
June 7, 2019 at 5:03 pm

#3651109

We have to migrate some databases to a new server.
Originally the old server is using server collation Latin1_General_CI_AS.
and hence the databases created on that using the same collation Latin1_General_CI_AS.
Now we want to new server to be collation: SQL_Latin1_General_CP1_CI_AS, since it will future host some additional vendor product.
Is it going to be an issue with the database I moved from older server to the new server?
When I move the database over, I am sure it will still keep the old collation, unless I use import data method to recreate, which I don't want to do that.

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

Collation is stored at DB level so when you restore your DB into the new server the collation will remain the same. You could look at altering the collation after you have restored the DB, something like this, obviously ensure you TEST before you put into production.

Change the DB collation then run

SELECT concat
        ('ALTER TABLE ', t1.TABLE_SCHEMA, '.',    t1.table_name, ' MODIFY ', t1.column_name,  ' ',  t1.data_type, '(' ,  CHARACTER_MAXIMUM_LENGTH, ')',' CHARACTER SET SQL_Latin1_General_CP1_CI_AS;'   )
from 
    information_schema.columns t1
where 
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('SQL_Latin1_General_CP1_CI_AS');

***The first step is always the hardest *******

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

where you may find some issues is on queries that do cross database queries or those that create temp tables.

Mainly the temp tables will have collation conflicts unless the queries handle that with collate compares.

If the only reason to change server collation talk with your guys and see if you should force the vendor to do their code accordingly (e.g. have their db on SQL... but have their code behave correctly if the Instance and tempdb are on a different collation) or if your company has to bend and follow their collation for the whole server.