7

I have an existing database that uses the Arabic_CI_AS collation, but I need to convert it to SQL_Latin1_General_CP1_CI_AS the default collation for the server, but the problem is when I use the following command:

USE master;
GO

ALTER DATABASE MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

It give me the following error message:

Msg 5075, Level 16, State 1, Line 1
The object 'GetAllSuppliersInCompliance' 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.

Msg 5075, Level 16, State 1, Line 1
The object 'GetTier1SupplierComplianceStatus' 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. .....

Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'MyDB' cannot be set to SQL_Latin1_General_CP1_CI_AS.

So I tried the following to disable the constraints temporary using the following command:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I then executed the first command, however the same error appeared.

6

Assuming those objects are stored procedures or views (and not tables), you'll need to:

  1. script the objects in question
  2. drop them from the database (or just remove the schema-binding)
  3. change the database collation
  4. recreate the objects by running the scripts you created in the first step.

As an aside, you didn't need to sp_msforeachtable "ALTER TABLE..."

  • Thanks, but some of the object were tables, so I had to do the following as a work around: 1) get the source database structure via scripts generator, 2) create blank database with the right collation, 3) execute the scripts on the blank database, 4) migrate the data from source to target. – Ahmad Abuhasna Jul 21 '14 at 14:37
  • 1
    @AhmadAbuhasna, I think the errors you receive are because of schemabound objects, like functions, stored procedures, views. I would go with @Max Vernon's recommendation, with the exception that instead of dropping objects, I would just remove WITH SCHEMABINDING, perform the collation change on the database, then re-apply WITH SCHEMABINDING. This in order to avoid losing permissions, extended properties, etc that you might overlook by mistake when scripting the object. Also, I'm not sure that changing the collation of the database will automatically change the collation of every table. – Daniel Hutmacher Aug 22 '14 at 11:07
  • Hello Daniel: In-fact I retired to do Max steps and it worked but had to remove one line from the procedures that caused the problem with the database conversion. but if I remove the schemabinding it worked fine. thanks – Ahmad Abuhasna Sep 12 '14 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.