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-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 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
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 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
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

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

Thanks
Blesson
blessonblessons
blessonblessons
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 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
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 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
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 420
No worries Phil,my email address is blessonblessons@yahoo.com.
afterx
afterx
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 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
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

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