nebb-tgr
asked on
SQL Collation
Hello,
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?
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?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
I will update when I get around to testing.
ASKER
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.
ASKER