Solved

Change Server Collation in SQL 2008 after installation

Posted on 2011-03-15
9
807 Views
Last Modified: 2012-06-21
Hello experts,

I need to change server collation in SQL 2008 from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AS. Is this possible?

Please advise.

Thanks and Regards
0
Comment
Question by:goprasad
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 35144263
you need to rebuild  the master database
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 35144295
0
 

Author Comment

by:goprasad
ID: 35144348
After dropping user databases, I need to run the following command, be selecting new query. Is the following fill up the blanks correct?

Setup /QUIET /ACTION=REBUILDMASTER /INSTANCENAME=<my instance name>
/SQLSYSADMINACCOUNTS=sa /[ SAPWD= StrongPassword ]
/SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 35144356
you dont need to drop the user dbs.
seems to be correct
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:goprasad
ID: 35144418
When I run the query I am getting the msg: Incorrect syntax near '/'
0
 
LVL 76

Expert Comment

by:arnold
ID: 35144568
As referenced in the link posted by aneeshattingal, can you use a per database collation settings as referenced in the Note:?

Note

Instead of changing the default collation of an instance of SQL Server, you can specify a default collation for each new database you create.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35145016
Try this as a one line command, remove the line breaks to get rid of the syntax error.

Bye, Olaf.
0
 

Author Comment

by:goprasad
ID: 35186900
I took the easy way out, re-installed the named instance with correct collation.
0
 

Author Closing Comment

by:goprasad
ID: 35186914
I had to re-install the SQL Named instance selecting the correct collation.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now