none
How do i change collation?

    Question

  • I created a new server registration under my SQL Server Group.  After creating the registration via EM, I looked under properties and the collation is SQL_Latin1_General_CP437_CI_AS.  It needs to be Latin1_General_BIN because this is the collation order in the production server.  Please bear in mind that I am not a DBA and this is all new to me, but unfortunately I'm the one who has to fix this.  Please keep the terms simple in any response.  Thanks.

     

    Tuesday, July 31, 2007 5:08 PM

Answers

  •  SteveS wrote:

     

    "...only Accounting and calculating intensive software uses BIN."

     

    And, apparently, Siebel.  Our production environment uses Latin1_General_BIN.

     

     

    BIN gives you the fastest sort but it is also the most restrictive collation best left to the database level because everything installed in a BIN collation server must the case sensitive.

    Tuesday, July 31, 2007 8:02 PM

All replies

  •  

    BTW, I already tried using ALTER DATABASE <dbname> COLLATE Latin1_General_BIN, but this only changes it at the database level and my application apparently doesn't like that the default collation order of the server is different.

     

    Tuesday, July 31, 2007 5:11 PM
  • You have to change all the collations of the tables and BIN(binary sort) require case sensitive data including your queries.  You have to look for script or manually ALTER each table with Enterprise Manager, it will take some time because it drops your original table and create a new one under the hood.  Post again if you still need help.

     

    Tuesday, July 31, 2007 5:19 PM
  • If you want to change the default collation for the server, the easiest way may be to uninstall SQL Server, and re-install SQL Server, selecting the proper collation. Of course, that depends upon your current usage, number of databases, etc.

     

    You can set default collations for databases, but if there are tables in the databases, you will have to manually change each table. (New tables will take the default collation.)

    Tuesday, July 31, 2007 5:25 PM
    Moderator
  • I think you need to rebuild your system dbs and specify the new collation option there in sql 2005 but i am not sure about sql 2000........just check this link if it helps in anyway,

    http://www.leyton.org/diary/2004/08/12/sql-server-and-collation-settings/

     

     

    Tuesday, July 31, 2007 5:27 PM
    Moderator
  • There are literally thousands of tables in the database.

     

    I do have the luxury, however, of being able to create a new database instance and DTS'ing from production.  I am concerned, however, that if I create a new database instance, set it up with the correct collation, and import data that I'll still run into a problem because the server registration instance will still have the wrong collation.  Do you think this will be okay?

    Tuesday, July 31, 2007 5:44 PM
  • Deepak,

     

    The link you gave looks useful.  Here's the text:

     

  • Make sure you’ve a recent backup of everything!!!
  • Get copies of sysaltfiles (which locates the underlying data file and log files)
  • Detach all the user databases (spdetachdb)
  • Shut down SQL Server
  • Run rebuildm.exe selecting the correct collation (The ‘SQL Collations’ “Dictionary order, case-insensitive, for use with the 437 (U.S. English) Character Set” is the match for SQLLatin1GeneralCP1CI_AS),
  • Recreate logins
  • Reattach the databases with spattachdb ‘dbname’,'file1′,’file2′ using the data from sysaltfiles

     

    I don't understand bullet 2, nor do I understand how to run rebuildm.exe.  Is that a command line application?

     

    Also, I don't like the sound of "Recreate logins".

     

Tuesday, July 31, 2007 5:48 PM
  • This is related to collation conflict between the Master, TempDB and some specific language collation, you do not need to rebuild a server to change collation since SQL Server 7.0.

     

      I also will not advice a user to change server to BIN(binary sort) in any language because BIN require case sensitive data so all databases in that server will require case sensitive data.   That will not be feasible if some of your databases are from packaged software from any vendor including Microsoft because only Accounting and calculating intensive software uses BIN.

     

    Tuesday, July 31, 2007 5:50 PM
  •  

    "...only Accounting and calculating intensive software uses BIN."

     

    And, apparently, Siebel.  Our production environment uses Latin1_General_BIN.

     

    Tuesday, July 31, 2007 7:57 PM
  •  SteveS wrote:

     

    "...only Accounting and calculating intensive software uses BIN."

     

    And, apparently, Siebel.  Our production environment uses Latin1_General_BIN.

     

     

    BIN gives you the fastest sort but it is also the most restrictive collation best left to the database level because everything installed in a BIN collation server must the case sensitive.

    Tuesday, July 31, 2007 8:02 PM