Question by: Arun Iyer    Answered by: David Klein    Last Updated: 15/11/2007 12:02:16 PM
Spot an error? Please comment about this article

Question:
How do I change the collation order in my SQL Server 2000 or 7.0 database?
OR
I want to know how to fix the Database Collation differs from Server Collation Rule in SSW SQL Auditor
OR
I am getting the error 'Collation order on the two databases is different. SSW SQL TOTAL COMPARE cannot compare databases with different Collation orders'. I assume I need to change the collation order in my SQL Server database, how do I do that?

Answer:

Change the Collation of your Database

Using SQL Server 7.0

In SQL 7.0, there can be only one Collation per server, so to change the Collation of a Database you need to change the Collation of the server itself. A Database with a Collation that differs from the server cannot be attached to that server. Solutions include setting up another SQL Server with the desired Collation and transferring the Database using DTS or using BCP to export the data before rebuilding the Server with the new Collation.See SQL Server Suggestion - Change the collation

Using SQL Server 2000 and Above

Unlike SQL 7.0, 2000 does support different collations for each database on the one server. The ALTER DATABASE COLLATE command will only change the *DEFAULT* collation of the database, it will not change collation of existing objects (e.g. columns & tables). They will still be stored in the old collation order even if the default is changed. You'll need to

• Individually alter existing columns to the desired collation (you can generate a script based on sysobjects if you want to follow this path) - but this is more complicated and not recommended. See Altering Collation for columns on all tables in a database OR
• Recreate the tables with a script excluding the collation settings (as described below) and DTS the Data Into the new structure

Servers have a default Collation which is inherited by new Databases where a Collation is not specified.
Databases have a Collation. Either unspecified (which uses the Server default) or a specified Collation.
Columns have a Collation. Either unspecified (which uses the Database Collation) or a specified Collation.

Warning: these steps assume that the Database uses a single Collation for all objects specified at the Database level. If you require different Collations for individual columns then do not proceed.

Steps for SQL Server 2000

1. In Enterprise Manager: Generate a SQL Script that will recreate the Database.
   Right-click on the Database > All Tasks > Generate SQL Script…
   Show All > Tick All Objects
   
   
   
2. Save the script to disk for later use.
3. In Enterprise manager, make sure that the database is not in use by checking  Management > Current Activity > Process Info. The Database should not be listed under the Database column.
4. In Query Analyzer run the following script to get a list of the files that the database uses 
   

   USE MyDatabase
   GO
   SELECT name, filename
   FROM sysfiles
   GO
   USE master
    
5. Copy the results into a text editor for later reference.
    
6. In Query Analyzer run the following script to detach the database.
   
   
   sp_detach_db @dbname='MyDatabase'
   
     
7. In Windows Explorer: Rename the files that were listed in step 2 to <filename>OLD. E.g.
   rename MyDatabase.mdf to MyDatabaseOLD.mdf
   Note: Data files are usually in C:\Program Files\Microsoft SQL Server\MSSQL\Data\
8. In Query Analyzer: Reattach the database with the name MyDatabaseOLD
   
   
   EXEC sp_attach_db
       @dbname = N'MyDatabaseOLD',
   @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabaseOLD.mdf',
   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabaseOLD_log.ldf'
   
    
9. Right-click on Databases and choose Refresh to show the renamed database.
10. In Query Analyzer: Open the saved SQL script and search the SQL Script for any COLLATE xxx statements and delete them.
    Eg. Find "COLLATE SQL_Latin1_General_CP1_CI_AS"
    and replace with nothing (empty string).
    This means that the database and columns will use the Collation specified as the server default collation.
    
    
    
11. Optional: To use a particular Collation for the Database add a COLLATE statement to the CREATE Database statement and specify the desired Collation. If a collation is not specified the server default collation will be used. Removing all COLLATE statements means that the server default collation will be used.
12. In Query Analyzer: Save and run the SQL Script to create a new Database with the desired collation.
13. In Query Analyzer
    
    -- We are going to use DTS and DTS does not add data in the relationship order
    -- Therefore we need to turn off the relationship data check temporarily
    EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    -- and turn off all triggers temporarily
    EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
    
    
    
14. In Enterprise Manager: Right-click on the Database with the incorrect Collation (the 'OLD' database) and choose All tasks > Export Data…  > Next
15. Check the Source Database settings are correct (e.g. MyDatabaseOLDCollation) then press Next
16. Select the new empty Database as the Destination. E.g. MyDatabase
17. Select 'Copy Objects and Data between SQL Servers' > Next
18. (see image below)
    Note: Don’t miss the tick on 'Use Collation'
    
19. Next > Next > Finish
20. In Query Analyzer
    
    -- Finished with DTS
    -- Therefore turn on the relationship data check again
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    -- Therefore turn on the triggers again
    EXEC sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
    
    
    Which Collation to Use?

There is no 'recommended' Collation as different Collations will be used in different countries. As a guideline:

• United States installations should use the SQL_Latin1_General_Cp1_CI_AS Collation
  
• Non-United States installations in English speaking countries (eg. UK, Australia) should use the Latin1_General_CI_AS Collation.
  

Once a Collation has been decided on, it should be used for all servers in your organisation.

More Information

SQL Server Books Online
Topic: Data Conversion and Transformation Considerations
Section: Code Pages, Collation, and Non-Unicode Data Issues
Section: Copy SQL Server Objects Task

SQL Server Architecture - Changing Collations
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_819v.asp

Keywords: Hot Tip - SQL Server 2000  Hot Tip - SQL Server 2005    

New Search SSW Custom Support