Click here to monitor SSC
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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 420
Will help you change the collation.It is good that you have backed up the database and scripted out all the user defined jobs associated with the instance.Also script out the user logins.

If you have customized any other setting at the server level then make note of them.Run sp_configure to get all the server level setting.

The command to change the server level collation is to:-

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName


This will basically rebuild the system databases with the new desired collation.This method is faster than reinstalling SQL Server.

refer the below link

http://msdn.microsoft.com/en-us/library/ms179254.aspx
blessonblessons
blessonblessons
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 420
In case you are wondering where the setup file is you will find it on the install CD or depending on whether it is a x86 (32-bit) install or x64 (64-bit) install the file location will vary.

eg:- for x64 (C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2)
philip.kraft
philip.kraft
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 54
Perfect! Thanks. I actually just managed to make a success of it on my local machine with the script you gave me! Server Collation was changed from Latin1_General_CI_AS to Latin1_General_CI_AS_KS_WS with no issues!

I will try it on the sandbox server and let you know.
Thanks so much.
Phil.
philip.kraft
philip.kraft
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 54
No luck on the server like I had on my local instance Crying

Y:\>setup /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=REBUILDDATABASE /INSTANCENAME
=MSSQLSERVER /SQLSYSADMINACCOUNTS=MyDomain\MyUsername /SAPWD=thesapassword /SQLCOL
LATION=SQL_General_CP1_CI_AS

Microsoft (R) SQL Server 2008 R2 Setup 10.50.1600.01
Copyright (c) Microsoft Corporation. All rights reserved.

The following error occurred:
The state of your SQL Server installation was not changed after the setup execut
ion. Please review the summary.txt logs for further details.

Error result: -2068643838
Result facility code: 1203
Result error code: 2
blessonblessons
blessonblessons
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 420
Can you please attach the log file for review?

Thanks
Blesson
blessonblessons
blessonblessons
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 420
The log file will be placed in the folder:-

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log
philip.kraft
philip.kraft
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 54
Thank you again John for all the help! Do you perhaps have an email address I can forward the logs to?
blessonblessons
blessonblessons
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 420
No worries Phil,my email address is blessonblessons@yahoo.com.
afterx
afterx
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 171
philip.kraft you can run this on sql 2005, 2008, and 2008 R2. You just have to make sure the server collation is different from the one you want to go to. More information can be found Here

-JM
wjewell
wjewell
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 55
sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1_CI_AS" -sSQLEXPRESS

Thanks for the command. I used it on my sqlexpress 2012 version. Just had to stop the instance, ran the script and then started my instance again.

Thanks Agian
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