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?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER