SQL Collation

Posted on 2011-10-06
Last Modified: 2013-04-26

We have a server containing several databases with tables containing cross references, views, stored procedures, triggers etc.
The problem is that one database has the wrong collation.

We are now experiencing errors related to this, because "equal to"-queries are being run. We can stop the system for a while in order to change this collation, but we are not sure how to do it.

As I understand it, running:
ALTER database_name COLLATE collation_name
will not change the columns in the database, leaving us with the same problem.

My idea is to export all data to temporary tables, drop the old tables, create new ones with new collation, import data from temp tables.
The problem with this is that there are dependencies that run across the tables. Wouldn't I have to make sure all tables are created in the correct order? Would it be possible to make a query that runs through the database creating scripts that I could use to recreate the database?

Question by:nebb-tgr
    LVL 25

    Accepted Solution

    Alter database will not change the collation where it is set explicitly. It can change the behaviour where it is not set.
    But problem is that you probably will get errors on the alter database statement if you have UDF's or indexes that depend on the default collation.

    LVL 67

    Assisted Solution

    Above mentioned blog clearly says (and that is my knowledge, too):
    ALTER DATABASE .. ALTER COLLATION command will change database collation for you, but all objects will stay "as is" with old collation (by design). The only way to change collation for all existing objects is to create a new empty database with new desired collation and copy data into this new database (copy wizzard, for example).
    That is, the DB collation will not be inherited by the existing columns, no matter if explicit collation has been assigned. So the first two sentences above are not correct. But the script posted in the blog is very helpful, and should do the job.
    LVL 1

    Author Comment

    I have downloaded the script in the first post, and will try that when I get access to the system. I will post the result and accept the solution if it works :)
    LVL 39

    Assisted Solution

    From my point of view and having to do that during a "globalization" project it was easier to get ALL table.columns from the DB that are text(char,varchar,text,etc... just do a select * from systypes and get the appropriate xtype) then put them in a cursor for instance and dynamicaly generate the ALTER TABLE...ALTER COLUMN.... statements (PRINT not EXEC so you can run them manualy) with desired collation. I'm not saying it will be easy but I found it much better than migrating all data for large DB (7+TB) into a new one.
    LVL 1

    Author Comment

    I'm sorry I haven't had time to close this question yet, but the solution has yet to be tested.

    I will update when I get around to testing.
    LVL 1

    Author Closing Comment

    We never got around to testing it. In the end, the system went through a big system upgrade where we dropped all tables and recreated them.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now