x

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:

  1. Scripting (saving to a file) the view,

  2. Dropping the view,

  3. Changing the Collation Code on the database

  4. 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.

more ▼

asked Apr 08, 2010 at 05:38 AM in Default

Jesper Klügel gravatar image

Jesper Klügel
11 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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
more ▼

answered Apr 08, 2010 at 07:08 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 08, 2010 at 08:50 AM

Henrik Staun Poulsen gravatar image

Henrik Staun Poulsen
579 13 15 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1947
x22

asked: Apr 08, 2010 at 05:38 AM

Seen: 6519 times

Last Updated: Apr 08, 2010 at 07:08 AM