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 Friday, March 09, 2012 6:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:02 PM
Points: 55, Visits: 409
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



Post #1264309
Posted Friday, March 09, 2012 6:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:02 PM
Points: 55, Visits: 409
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)

Post #1264322
Posted Friday, March 09, 2012 6:23 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
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.
Post #1264324
Posted Friday, March 09, 2012 6:35 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
No luck on the server like I had on my local instance

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
Post #1264334
Posted Friday, March 09, 2012 7:38 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:02 PM
Points: 55, Visits: 409
Can you please attach the log file for review?

Thanks
Blesson
Post #1264398
Posted Friday, March 09, 2012 8:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:02 PM
Points: 55, Visits: 409
The log file will be placed in the folder:-

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

Post #1264423
Posted Monday, March 12, 2012 1:00 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
Thank you again John for all the help! Do you perhaps have an email address I can forward the logs to?

Post #1264988
Posted Monday, March 12, 2012 1:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:02 PM
Points: 55, Visits: 409
No worries Phil,my email address is blessonblessons@yahoo.com.
Post #1264992
Posted Sunday, March 18, 2012 7:56 PM
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
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
Post #1268783
Posted Thursday, April 10, 2014 3:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:27 AM
Points: 1, Visits: 15
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
Post #1560320
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse