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

Server and user databases using different collations Expand / Collapse
Author
Message
Posted Tuesday, June 8, 2010 6:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 246, Visits: 2,096
Hi,

I have managed to end up with a server and its' system databases using Latin1_General_CI_AS and the user databases using SQL_Latin1_General_CP1_CI_AS.

All other servers use SQL_Latin1_General_CP1_CI_AS for system and user databases, so this would be out "standard".

Is there anyway to change the server/system databases to use SQL_Latin1_General_CP1_CI_AS without affecting the user databases, if possible I want to avoid having to export objects and data from the user databases, drop the dbs and then recreate and reimport the objects and data.

Any help much appreciated.
Post #933952
Posted Monday, August 11, 2014 2:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 29, 2014 10:46 AM
Points: 2,734, Visits: 943
I got the same problem.

A staging server is using collation_B and the Production server is using collation_A
Te staging server host a lot "unrelated with the problem" DB from other depts.

searching on web and at this very forum found 2 interesting articles:

http://www.sqlservercentral.com/articles/Collation/72182/
http://www.sqlservercentral.com/articles/Collation/70685/


As you can see it can be done but it's not simple.

If you got few DB a reinstall can be a option.
For me I considering to create a new instance with the correct collation and just move the few staging DBs to it

Hope it helps
Post #1602072
Posted Tuesday, August 12, 2014 2:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
If you want to change the system collation then you can rebuild the system databases from the install media.

You will want to script out all server objects like, jobs, logins, linked servers, DDL triggers etc.

Make sure you have backups of all your user databases and system databases as you will need to re-attach / restore them as the master database will know nothing about them as it will be like its a fresh server.


http://msdn.microsoft.com/en-gb/library/dd207003(v=sql.105).aspx




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1602174
Posted Tuesday, August 12, 2014 2:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:43 AM
Points: 1,130, Visits: 1,391
jcb (8/11/2014)

If you got few DB a reinstall can be a option.
For me I considering to create a new instance with the correct collation and just move the few staging DBs to it

Hope it helps

I also follow the same path - create new instance (if it is feasible) with correct collation and move the DBs to it.


Thanks
Post #1602176
Posted Tuesday, August 12, 2014 4:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 5,415, Visits: 10,069
Be careful when you do this, because if someone has already compensated for the collation difference by putting COLLATE Latin1_General_CI_AS clauses throughout the code, then things may start breaking.

John
Post #1602217
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse