SQLServerCentral.com / Article Discussions by Author / Article Discussions / Discuss content posted by Kevin Sermijn / Change Server Collation in SQL Server 2008 / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 06:48:09 GMT20RE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxNice post.[url=http://www.sqlsuperfast.com]www.sqlsuperfast.com[/url]Thu, 17 Mar 2011 00:31:01 GMTa.rajmaneRE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspx[quote][b]UMG Developer (3/15/2011)[/b][hr]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.[/quote]Good pick up!Tables within each DB will not only keep their old collation for pre-existing columns, but newly created columns will have the new server collation, leading to a nightmare scenario of mixed collations within the same table.I can contribute this script, the major part of it borrowed from another SQLServerCentral contributor (sorry I did not preserve the name!). I tested it on a few databases and think it will work but - as usual - test it first !!!!!! ALTER PROCEDURE [dbo].[usp_CollationChangeAllTables]as--USAGE: USE THIS DB ------- very very important--USE thisDB-- EXEC DBA..usp_CollationChangeAllTablesbegin--ALTER TABLE dbo.tblXXX DROP CONSTRAINT --PK & FK are not character columns so they can staydeclare @NewCollation varchar(255) ,@Stmt nvarchar(4000) set @NewCollation = 'Latin1_General_CI_AS' -- change this to the collation that you need--WAS 'SQL_Latin1_General_CP1_CI_AS'declare @CName varchar(255) ,@TName sysname ,@OName sysname ,@Sql varchar(8000) ,@Size int ,@Status tinyint ,@Colorder intdeclare curcolumns cursor read_only forward_only localfor select QUOTENAME(C.Name) ,T.Name ,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name) ,C.Prec ,C.isnullable ,C.colorder from syscolumns C inner join systypes T on C.xtype=T.xtype inner join sysobjects O on C.ID=O.ID inner join sysusers u on O.uid = u.uid where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext') and O.xtype in ('U') and C.collation != @NewCollation and objectProperty(O.ID, 'ismsshipped')=0 order by 3, 1open curcolumnsSET XACT_ABORT ONbegin tranfetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorderwhile @@FETCH_STATUS =0BEGIN set @Sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName SET @Sql = @Sql + CASE WHEN @Size = -1 THEN + '(MAX)' ELSE isnull ('(' +convert(varchar,@Size)+')', '') END SET @Sql = @Sql +' COLLATE '+ @NewCollation +' '+case when @Status=1 then 'NULL' else 'NOT NULL' end exec(@Sql) -- change this to print if you need only the script, not the action PRINT @Sql fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorderendclose curcolumnsdeallocate curcolumnscommit tranEND --procSee alsohttp://www.sqlservercentral.com/Forums/Search1-0-2.aspx?SessionID=cnn0n2an3ejd3w45lgky3345&SortBy=2&SortOrder=1(Keep in mind TheSQLGuru's warning, all these steps will change the collation for the server, the databases default, then the table defs and will guarrantee consistency for the future, but it is not clear what happens to the data already existing in each table.)Tue, 15 Mar 2011 18:36:27 GMTOl'SureHandRE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxThis doesn't cover the cross-instance collation issues.Using the default configuration for Linked-Servers will give you no end of headaches.By default, the collation is eqated to the collation of the remote server/database.TIP: If you have a linked server, make sure it's collation is set to the local instance/database.that way you don't care what the remote collation is (even if it changes) as all 'data' will treated as though it were the local collation.Tue, 15 Mar 2011 14:40:54 GMTrob.lobbe-964963RE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxMauve, 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.Tue, 15 Mar 2011 12:33:26 GMTPhilip BarnardRE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxFirst 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 [u]default in the U.S. (English (United States))[/u]! Ref: [url]http://msdn.microsoft.com/en-us/library/ms143508.aspx[/url] 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.Tue, 15 Mar 2011 11:27:04 GMTMauveRE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxI'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.Tue, 15 Mar 2011 11:00:16 GMTPhilip BarnardRE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxGreat 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,--JimTue, 15 Mar 2011 08:07:44 GMTJames StephensRE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxThanks 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.Tue, 15 Mar 2011 04:12:31 GMTNick W*RE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxIn 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 ?Tue, 15 Mar 2011 02:29:46 GMTexmarouane2RE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxHad 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,[url=http://www.schmitzit.com]Peter Schmitz[/url]Tue, 15 Mar 2011 02:17:19 GMTpsc 24632RE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxHi 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\Internationalany advise - is much awaited .. thanks :)Tue, 15 Mar 2011 01:29:29 GMTkiran_w1RE: Change Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxI 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.Tue, 15 Mar 2011 00:31:00 GMTUMG DeveloperChange Server Collation in SQL Server 2008http://www.sqlservercentral.com/Forums/Topic1078120-2919-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Collation/72182/">Change Server Collation in SQL Server 2008</A>[/B]Mon, 14 Mar 2011 22:26:44 GMTsqlkevin