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 on SQL Server 2008 Expand / Collapse
Author
Message
Posted Saturday, September 18, 2010 1:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:11 PM
Points: 55, Visits: 414
Comments posted to this topic are about the item Changing Server Collation on SQL Server 2008
Post #988773
Posted Monday, September 20, 2010 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 18, 2012 7:54 PM
Points: 3, 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
Post #989516
Posted Monday, September 20, 2010 11:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:48 AM
Points: 430, Visits: 312
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
Post #989619
Posted Monday, September 20, 2010 11:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:11 PM
Points: 55, Visits: 414
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
Post #989631
Posted Monday, September 20, 2010 4:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
thanks for the article.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #989830
Posted Tuesday, September 21, 2010 5:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:48 AM
Points: 430, Visits: 312
If you need such a script, then I've got it

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

Post #990159
Posted Monday, September 19, 2011 2:02 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:49 PM
Points: 221, Visits: 244
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
Post #1177455
Posted Friday, March 9, 2012 2:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 10:29 AM
Points: 5, 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!
Post #1264195
Posted Friday, March 9, 2012 5:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:11 PM
Points: 55, Visits: 414
Hi Phil,

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

Thanks
Blesson.
Post #1264297
Posted Friday, March 9, 2012 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 10:29 AM
Points: 5, 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.
Post #1264301
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse