Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database collation Change Expand / Collapse
Author
Message
Posted Monday, April 28, 2014 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 3:40 AM
Points: 3, Visits: 31
Hi my Production DB's Server level Collation is SQL_Latin1_General_CP1_CI_AS
where as my development DB's Collation is Latin1_General_CI_AI ,often our development
team asks us to create a copy of the Production DB on our development Environment this becomes a big task for us because when we create a new DB and restore it with production copy the it will have a collation(SQL_Latin1_General_CP1_CI_AS) which is different from our Development server's collation

Can anyone please tell an easier way of changing the Collation of newly restored DB(without changing the Server level collation of our development DB)
Post #1565492
Posted Monday, April 28, 2014 4:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:45 PM
Points: 2,242, Visits: 2,701
You can change the (default-)collation of a database with the ALTER DATABASE command. But this doesn't change the collation of the existing objects!! You need to change each existing object to the desired collation with an ALTER command. There's a script on codeplex to help you with this:
http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1565498
Posted Monday, April 28, 2014 4:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Usually collation should be same on Prod and dev. Backup of the database also contain the details of collation and while restore it will keep the same collation. The only option is ALTER DATABASE command to change the collation.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1565506
Posted Monday, April 28, 2014 6:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 12,890, Visits: 31,849
changing the collation of the database is only a tiny 1% piece of the puzzle.

every varchar/char/nvarchar/nchar/text column in the database has it's own collation, that is not affected just because you change the database collation.

you have to change the individual columns as well, which can get very complex if defaults, check constraints, indexes or several other things are applied to any of those data type columns.

the default collation only affects newly created items that do not explicitly define their collation.

i made a script to change collation once, it worked for my situation, as i remember it it was a monster at 600 lines of code that took all those things into consideration.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1565553
Posted Wednesday, April 30, 2014 2:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 207, Visits: 823
Wouldn't creating a new DEV instance with server collation matching that of your production server be easier.
Post #1566271
Posted Wednesday, April 30, 2014 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
As others have pointed out, changing the collation for a database is not simple.
However, if you need to change it it's probably because some comparisons between columns in your database and columns in #temp tables are failing.

I see a couple of options here:

1) Add "COLLATE DATABASE_DEFAULT" to each CREATE #temp TABLE statement and/or directly in the comparison predicate. Not easy, but it will make your application more robust.
2) Change the dev server collation. Obviously, it will be feasible only if you don't have other databases in the dev instance that require a different collation
3) Build a script to change the collation of every column in the restored database, as Lowell suggested.

Lots of pain, no matter which one you choose.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1566410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse