SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change Server Collation in SQL Server 2008


Change Server Collation in SQL Server 2008

Author
Message
sqlkevin
sqlkevin
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 166
Comments posted to this topic are about the item Change Server Collation in SQL Server 2008
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2662 Visits: 2204
I think this article is a great start, but it ends too early, just using ALTER DATABASE only changes the default collation, it doesn't change existing tables, columns, or the data in the tables.
kiran_w1
kiran_w1
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: 13
Hi I get this error with one my MFC application -

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1254_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

I am trying to address once for all at application irrespecitve what collation the database is using - as this app can be used in diffrent places , also the user may want to support local language chars in the application - which have to be stored , fetched from DB.

As of now my DB is built with SQL_Latin1_General_CP1_CI_AS - which is a default.

I am trying to figure out what changes i would have to do in Application to achive this goal.

Or tweaking - international settings of ( PC host which hosts my application)

under HKEY_CURRENT_USER\Control Panel\International

any advise - is much awaited .. thanks Smile
psc 24632
psc 24632
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 139
Had to do this for a testserver just the other day. Perhaps it would have been nice to mention that the default instance of SQL Server should be specified in the command-line setup command as MSSQLSERVER, and that the login list is space seperated.

Additionally, due to running the command in QUIET mode, it might be hard to see whether the change succeeded or not. I spent a few tries repeatedly clicking print screen to capture the screen output before the window closed, and that way found out there will be a logfile written to:

%Program Files%\Microsoft SQL Server\100\Setup Bootstrap\Log\summary.txt.

Best,

Peter Schmitz

MsSqlForum - A forum for MS SQL discussions
exmarouane2
exmarouane2
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 17
In this example the staging collation is Latin1_General_CI_AS and the production collation is SQL_Latin1_General_CP1_CI_AS. We will change the staging collation to SQL_Latin1_General_CP1_CI_AS.



From the names of the collations, _CI_ means case incensitive. Initially you were trying to change the collation because case sensitivity is différent.
Is there a typo ? Or this is another example ?
Nick W*
Nick W*
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 180
Thanks for this, I could have done with your nice scripting bits the other day, when we discovered one of our clients had installed their warm standby server with the wrong collation. ;-) D'oh.

Unfortunately, the changing of the Collation for databases fails if you have any objects with a schema lock.... And for some reason I can't yet figure, it seems that some functions are arbitrarily marked as having schema locks. They don't specify collation or anything... Not sure what's going on there... Anyone have any ideas?

Word to the wise, though: If you are changing both the collation of your server AND your DB, Take a backup of the Db, restore it under a test name, and try to change its collation BEFORE you start this process... If it succeeds, by all means, carry on. But at the moment, I'm having to put up with one Db with a legacy collation, while all the others and the server are playing nicely together.
James Stephens
James Stephens
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 210
Great stuff. I had to change collation a while back because even though all production db's were in the out-of-box default collation (we had inherited a newer more powerful sql-server box from a failed project and restored the db's to this new box), what we didn't check was that the instance on that new box had been installed with a non-default collation.

In most cases everything was fine--but since the server creates #Temp tables in tempdb under the server default collation--and these tables often join normal tables in stored procs--in many cases with a join to #temp on a char field the procedures would fail. This was insidious because those circumstances were not easily identifiable.

Thanks for the article,
--Jim
Philip Barnard
Philip Barnard
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 46
I've been through this process before several years back. We changed our collation from Latin1_General_CI_AS to SQL_Latin1_CP1_CI_AS. It wasn't for any reason other than to make sure that everything in the instance was the same collation (due to temp tables and cross DB queries). Like mentioned in an earlier post the column collation change was the toughest part of the conversion.

In the end we wrote a script that dropped FK's, PK's and indexes, then updated the collations on each column that applied, finally adding all the FK's, PK's and indexes back in after the collation updates were done. There was a lot of dynamic SQL in that script the way it was written. However, this was all done with a SQL 2005 instance. Not certain if the old script would work in SQL 2008 or not.

We have a project coming up where we may have to do this again in SQL 2008, but we haven't had time to test.
Mauve
Mauve
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1736 Visits: 2054
First off, the "SQL_" prefixed default collation for a new installation of SQL Server is bad, bad, bad! This is a legacy collation going back to the "dark ages", e.g., SQL Server 6.5, 7. The recommended collation is the Windows collation. To make matters worse, the "SQL_" collation is only the default in the U.S. (English (United States))! Ref: http://msdn.microsoft.com/en-us/library/ms143508.aspx You will see that, for most installations, the default is "Latin1_General_CI_AS".

So if you are starting a new instance of SQL Server, choose the proper, and not a legacy collation, during the instance creation.

That said, you still need to be able to operate in a mixed collation environment. There is no mandate that all databases must have the same collation in the instance. For example, you may acquire a 3rd party application that requires a case sensitive database collation. There is no reason that the database for that application cannot be installed and running in your case insensitive SQL Server instance.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Philip Barnard
Philip Barnard
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 46
Mauve, I find your post very interesting. First in my last post I had the order that I moved the collations backwards...we went from the SQL_ to Latin1_General_CI_AS. I hadn't seen that default collation page before. In the past I was looking for something just like that to show to management because I was the one that originally moved all of our newer instances to Latin1_General_CI_AS and the question has come up whether that was the right choice.

We have several 3rd party apps in place that require the SQL_ collation...but the origins of their apps was back in the dark ages as you say...so they have never changed. These apps require the entire instance to be the SQL_ collation...very annoying.

But thank you for the link that you provided. I'm going to leave things in the Latin1_General_CI_AS format...though I'll still have to have a few special instances laying around in the other format. Just the cost of business I suppose.
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