Changing Collation Code on SQL Database

I have a problem with Collation Code on some of my SQL 2005 Servers.

The servers are installed with Collation Code “Danish_Norwegian_CI_AS” and

The database is installed with Collation Code “Danish_Norwegian_CI_AI”

The Collation Code has to be the same on the database and SQL Server.

I have tried to change the collation Code on the database, but it came up with the following errors:

The object '_dta_mv_100' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

The object 'fXtSummary' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

I think I could handle the views by:

Scripting (saving to a file) the view,
Dropping the view,
Changing the Collation Code on the database
and then running the saved script to create the view again.

The functions are a bit more difficult, because there are some “Object Dependencies” .

I could script the function also, but I have a problem to recreate the “Objects that depend on [].

Can somebody help me with a solution to handle this problem or are there an other way to change the Collation Code? It is not possible to change the Collation Code on the SQL Server by reinstall SQL.

There are 185 views and 47 Functions with the depend on database collation on each SQL Server and I have 4 SQL servers with the same problem.

asked Apr 08 '10 at 05:38 AM in Default

Jesper Klügel
11 ● 2 ● 2 ● 2

(comments are locked)

2 answers:

You are going to have to script out the objects and create from new...

create a new database with the correct collation
then script out all the objects in the original database, ensuring that you script the collation option
find and replace any reference to the collation, changing it either to nothing (so will pick up database default) or the specific collation you need
execute the script
copy data (if any)
rename the old database,
rename the new database to the old one

answered Apr 08 '10 at 07:08 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

(comments are locked)

I posted this a while back: link text

It is not perfect, but it is a start.

Then Irwan Tjanterik posted (on page 4 of the link above) a note about a program that he had written to solve this problem. I got a copy, but have not had the time to check it out. You could write him, and see if he still needs testers.

Best regards,

Henrik Staun Poulsen
www.stovi.com

answered Apr 08 '10 at 08:50 AM