Msg 5075 - The database collation cannot be changed if a schema-bound object depends on it

15 September,2014 by Jack Vamvas

Question: I executed a ALTER DATABASE COLLATE statement and received the following error message. How can I fix and proceed with ALTER DATABASE COLLATE procedure?

ALTER DATABASE My database

COLLATE  Latin1_General_100_CI_AS_KS ;

 

Msg 5075, Level 16, State 1, Line 1

The object 'CK_MyCheckConstraint_Uid' 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.

Answer: The reason for this message is you haven’t met  one or more of these conditions:

1)      Must have an exclusive lock on the database - read more on SQL Server ALTER DATABASE , change collation and exclusive lock

2)      No schema-bound object can be dependent on the database collation. Schema-bound objects include: User defined functions (UDF) and views created with SCHEMABINDING

3)      CHECK CONSTRAINTS

4)      Computed Columns

5)      Table-valued functions   with character columns using collations inherited from the default database collation

In the case above , it appears there is a CHECK CONSTRAINT dependant on the database collation.

Proceed with scripting out the dependant objects, drop the objects  change the database collation and recreate the the oblects from the scripts.

Make sure you have a BACKUP in place before you proceed with these steps!!

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Msg 5075 - The database collation cannot be changed if a schema-bound object depends on it


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer