SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing server collation


Changing server collation

Author
Message
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4903 Visits: 9108

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 ?





John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14293 Visits: 15974
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.
Jersey Moe
Jersey Moe
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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


Brian Cidern-192506
Brian Cidern-192506
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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.


Gift Peddie
Gift Peddie
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6298 Visits: 14456

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
WiltsDBA
WiltsDBA
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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
Gift Peddie
Gift Peddie
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6298 Visits: 14456

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
RichB
RichB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1585 Visits: 1058

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...!





SQL ORACLE
SQL ORACLE
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2653 Visits: 1314
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.
Rishdin Ameer-354721
Rishdin Ameer-354721
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 23
i tried rebuildm.
but the sql_latin1_general_cp1_ci_as collation required couldnot be found.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search