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»»

Change Server Collation in SQL Server 2008 Expand / Collapse
Author
Message
Posted Monday, March 14, 2011 10:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 6:52 AM
Points: 0, Visits: 161
Comments posted to this topic are about the item Change Server Collation in SQL Server 2008
Post #1078120
Posted Tuesday, March 15, 2011 12:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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.
Post #1078143
Posted Tuesday, March 15, 2011 1:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, March 20, 2011 11:02 PM
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 :)
Post #1078165
Posted Tuesday, March 15, 2011 2:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 5:51 AM
Points: 5, Visits: 127
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
Post #1078176
Posted Tuesday, March 15, 2011 2:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 15, 2011 2:49 PM
Points: 3, 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 ?
Post #1078184
Posted Tuesday, March 15, 2011 4:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 22, 2012 8:55 AM
Points: 124, 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.
Post #1078213
Posted Tuesday, March 15, 2011 8:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 10, 2014 3:19 PM
Points: 28, Visits: 185
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
Post #1078358
Posted Tuesday, March 15, 2011 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 6:28 AM
Points: 4, 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.
Post #1078516
Posted Tuesday, March 15, 2011 11:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:22 AM
Points: 1,187, Visits: 1,984
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.
Post #1078542
Posted Tuesday, March 15, 2011 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 6:28 AM
Points: 4, 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.
Post #1078588
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse