Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

SQL Collation


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?

3 Solutions
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.

See http://blogs.msdn.com/b/ikovalenko/archive/2006/12/03/alter-database-alter-collation-forced.aspx
QlemoC++ DeveloperCommented:
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.
nebb-tgrAuthor Commented:
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 :)
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

lcohanDatabase AnalystCommented:
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.
nebb-tgrAuthor Commented:
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.
nebb-tgrAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now