Change Server Collation in SQL Server 2008

  • sqlkevin

    SSC Rookie

    Points: 46

    Comments posted to this topic are about the item Change Server Collation in SQL Server 2008

  • UMG Developer

    SSChampion

    Points: 13482

    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

    Grasshopper

    Points: 19

    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 🙂

  • psc 24632

    Valued Member

    Points: 57

    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[/url]

  • exmarouane2

    SSC Veteran

    Points: 233

    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*

    SSC-Addicted

    Points: 490

    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

    SSC-Addicted

    Points: 497

    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

    SSC Enthusiast

    Points: 176

    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

    SSChampion

    Points: 11316

    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.


    [font="Arial Narrow"](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.[/font]

  • Philip Barnard

    SSC Enthusiast

    Points: 176

    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.

  • rob.lobbe-964963

    Old Hand

    Points: 307

    This 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.

  • Ol'SureHand

    SSCrazy

    Points: 2744

    UMG Developer (3/15/2011)


    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.

    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_CollationChangeAllTables

    begin

    --ALTER TABLE dbo.tblXXX DROP CONSTRAINT

    --PK & FK are not character columns so they can stay

    declare

    @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 int

    declare curcolumns cursor read_only forward_only local

    for

    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, 1

    open curcolumns

    SET XACT_ABORT ON

    begin tran

    fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder

    while @@FETCH_STATUS =0

    BEGIN

    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, @Colorder

    end

    close curcolumns

    deallocate curcolumns

    commit tran

    END --proc

    See also

    http://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.)

  • a.rajmane

    Old Hand

    Points: 377

    Nice post.

    www.sqlsuperfast.com

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply