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 12»»

Changing server collation Expand / Collapse
Author
Message
Posted Tuesday, July 19, 2005 2:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:08 AM
Points: 2,827, Visits: 8,480

Way back, our server collation was set to Latin1_General_Bin to satisfy the requirement of some software running on that server. Since then, we've been using that server for other software that requires SQL_Latin1_GeneralCP1_CI_AS.   Since most applications we now have use SQL_Latin1_GeneralCP1_CI_AS, I would like to change the server default to SQL_Latin1_GeneralCP1_CI_AS, and leave that 1 original DB as Latin1_General_Bin.

What's the best way to do this ?




Post #202573
Posted Wednesday, July 20, 2005 4:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 5,386, Visits: 9,964
Assuming you are using SQL Server 2000 (SQL 7.0 doesn't support multiple collations on the same server), use rebuildm to rebuild the master database, specifying the new default collation.  Beware, though - this will change the collation of all system databases and could cause you problems if, for instance, your application creates temporary tables in tempdb and attempts to join them with tables from the original database.  Best to leave things as they are if everything is working at the moment.
Post #202695
Posted Wednesday, July 20, 2005 8:04 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 8:39 AM
Points: 54, Visits: 23

John:

I have the same issue with one of my servers.  The original use of the server was to host a datawarehouse generated from our mainframe.

However, overtime I've been asked to host several vendor applications which do not have the same collation.

I find it better to simply run an alter after the application insatll has setup the database.  Or if the database is created from scratch, just use the drop down list and pick the desired collation.

USE master

ALTER DATABASE database 
COLLATE < collation_name >

"If it ain't broke....."

 

JerseyMo

Post #202747
Posted Wednesday, July 20, 2005 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2005 12:35 PM
Points: 2, Visits: 1

Just an FYI for those attempting to use REBUILDM.

You need to be logged directly onto the console.  It will not work via an RDP connection.  Although, if you have access through a Lights Out interface, you may have success.

 

Post #202836
Posted Thursday, July 21, 2005 1:00 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:39 AM
Points: 3,428, Visits: 14,438

In SQL Server 2000 you don't need that, because you can add the collation to your create database and Table statement.  Rebuild was a requirement for SQL Server 7.0.  Hope this helps.

Kind regards,

Gift Peddie



Kind regards,
Gift Peddie
Post #203265
Posted Friday, May 26, 2006 4:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 8:53 AM
Points: 47, Visits: 184
You can choose the database collation in 2K according to your requierments. RebuildM.exe will wipe out all your logins, DTS packages etc. TBH you are better off changing the default collation and then converting your DB (painful, but worth it in the end). Having a master, or more of a pain, a tempdb at a different collation to your data database will cause you to jump through more hoops that you really want to (yup, this is the battle scarred voice of experience here). BTW has anyone gotr a full list of collations and what the are (for example, what is the code page of latin1_General_bin and is it unicode compliant?)

"Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
Post #283032
Posted Friday, May 26, 2006 11:14 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:39 AM
Points: 3,428, Visits: 14,438

I am not sure if you can use the list with SQL Server 2000 but the links below is the complete code page and collation listing for SQL Server 2005.   Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms144250(SQL.90).aspx

http://msdn2.microsoft.com/en-us/library/ms180175(SQL.90).aspx

 

Kind regards,

Gift Peddie



Kind regards,
Gift Peddie
Post #283200
Posted Wednesday, November 1, 2006 9:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:46 AM
Points: 1,070, Visits: 907

I second (third) the trauma of different collations in tempd and operational databases.  Its a right pain in the jacksy.

Backup your logins, and rebuild away.

Just remember there is a good chance that any database not in the server collation will suddenly start throwing errors...!

 




Post #319621
Posted Thursday, November 2, 2006 2:43 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
You can change collation for a database and a column. But if you would like to set your new collation as default, you have to rebuild master database.
Post #320108
Posted Thursday, May 17, 2007 1:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 28, 2011 8:28 AM
Points: 35, Visits: 12
i tried rebuildm.
but the sql_latin1_general_cp1_ci_as collation required couldnot be found.
Post #366653
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse