sql - Collation Error

Question

I am using Microsoft SQL Server Management Studio. I have two databases one is the system database, which has the master database and the other one is my database called CCTNS_CAS_DE_DB. When I am trying to generate the reports through the tool which uses the CCTNS_CAS_DE_DB database.

I get the following error:

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

I went through the SQL Server and checked the properties of the master database it shows the collation as Latin1_General_CI_AI but when I went to the properties of the CCTNS_CAS_DE_DB database it shows the collation as SQL_Latin1_General_CP1_CI_AS.

I searched for the error online but most of the solution tell how to change the collation of a particular table but I didn't come across any query which will change the collation my database to Latin1_General_CI_AI.

I came across one query which is:-

ALTER DATABASE CCTNS_CAS_DE_DB COLLATE Latin1_General_CI_AI

When I ran this query in my SQL Server it threw the following error:-

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'CCTNS_CAS_DE_DB' cannot be set to Latin1_General_CI_AI.

I know this question has already been posted here but that was in a different context I think.

question from:https://stackoverflow.com/questions/13785814/collation-error

Answer 1

Need to set it to SINGLE_USER first.

ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 

GO 

ALTER DATABASE [database] COLLATE SQL_1xCompat_CP850_CI_AS; 

GO 

ALTER DATABASE [database] SET MULTI_USER; 

GO