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


Changing Server Collation on SQL Server 2008


Changing Server Collation on SQL Server 2008

Author
Message
blessonblessons
blessonblessons
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 420
Comments posted to this topic are about the item Changing Server Collation on SQL Server 2008
afterx
afterx
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 171
There is also an undocumented way of doing this which is all automated. This will change the server and all databases on the instance. Of course this should be tested before running it on your production system, and make sure EVERYTHING is backed up...

1.) Find when your instance is running and use that path to find where sqlservr.exe is located.
2.) Check to ensure you have plenty of disk space.
3.) After the backups are done open a command prompt and navigate to where sqlservr.exe is located. If you are using Server 2003 just open an command prompt. If you are using Server 2008 be sure to open an admin command prompt.
4.) Then run the command below.
5.) Once you hit enter you will see the following



Command:
Replace latin1_general_ci_ai with whatever you want to change the instance too.

sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai"

or

sqlservr -m -T4022 -T3659 -q"latin1_general_ci_ai" -sINSTANCENAME

This has worked for me several times and is much quicker and easier than other methods.

-JM
Paul Smith-221741
Paul Smith-221741
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

Group: General Forum Members
Points: 502 Visits: 334
Rather than Changing the Collation on the server to Match the Database, Why not change the Collation on the database to match the Server.

As you correctly spell out, it leaves the collation on the individual columns in their original state, but it is a straightforward process to set up scripts to change them
blessonblessons
blessonblessons
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 420
One reason to change the server collation was because the database is replicated from another server and the collation for that server is different from the destination server.


Anyway changing database collation is not that straight forward.In my case I scripted out all the tables,procedure etc and replaced the collation with new one.Ran the script and then bulk inserted the data.

Or else a script to drop(disabling foreign keys also doesn't allow to change collation) all the foreign key and triggers etc,change collation of character fields and then finally place all the constraints back.(It would be great if anyone who has created such a script to share it).I managed to complete the first half (dropping/disabling foreign keys/triggers etc).But creating the foreign key became a tedious task as I had to keep track of quiet a few field and the last thing I wanted was to find that some of the foreign keys and triggers are missing after the go live happens :-D
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32284 Visits: 18552
thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Paul Smith-221741
Paul Smith-221741
Mr or Mrs. 500
Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)Mr or Mrs. 500 (502 reputation)

Group: General Forum Members
Points: 502 Visits: 334
If you need such a script, then I've got it:-D

It may need a bit of work but it does the majority of things

It runs as a total of 10 scripts.
the 1st 4 generate scripts of the things to put back afterwards
5th Removes All the bits that stop the collation changes (FK, PK, Contraints etc)
6th Changes the Collation on All (n)char, (n)varchar, (n)text columns
7 - 10 are the scripts generated from 1-4
DBA Cabuloso
DBA Cabuloso
Old Hand
Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)Old Hand (300 reputation)

Group: General Forum Members
Points: 300 Visits: 274
The undocumented process told by Alexf works until the 2005 version, in the 2008 it does not work. This is because the Server Collation in 2008 can't change unless it is restarted.

Dba Cabuloso
Lucas Benevides

________________
DBA Cabuloso
Lucas Benevides
philip.kraft
philip.kraft
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 54
Good morning JM.

I came across you article after trying to find a way to change or server collation for SQL Server 2008 R2 std.

Am I right the command you posted is "sqlservr -m -T4022-T3659 -q "COLLATION" -s "INSTANCENAME"?

This seems to only kick of the setup and I was wondering if you have any further experience in resolving Server Collation issues?

Any help would be massively appreciated!

Regards.
Phil - the struggling DBA!
blessonblessons
blessonblessons
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 420
Hi Phil,

Is this a production server and are there any user databases on that server?

Thanks
Blesson.
philip.kraft
philip.kraft
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 54
Currently this is our OpsManager server. All the user DB's have been backed up and detached. So its a bare instance. SQL Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM). I am trying the same on my local installation af tsql (Enterprise) But still no luck!?

Thanks for the quick reply!
Phil.
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