Menu
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.
833
You can resolve the issue by forcing the collation used in a query to be a particular collation, e.g. SQL_Latin1_General_CP1_CI_AS or DATABASE_DEFAULT. In the above query a. MyID and b. YourID would be columns with a text-based data type.
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.
Collation is a set of rules that tell database engine how to compare and sort the character data in SQL Server. Collation can be set at different levels in SQL Server.
Collation in Microsoft SQL Server can be complicated because you can have a separate collation set at: This method may also be used to move a SQL Server Database between two servers while ensuring the collation is correct. To setup your Microsoft SQL Server database correctly, see the following resources for each product:
Firstly, please check the collation of the table and columns that you imported into Power BI Desktop using T-SQL. If there is any difference between the collation, the above error would occur. Secondly, in SQL Server, change collation for problem columns with the T-SQL below.
If it returns any results, you must adjust the collation. Firstly, create a new database as per the guidelines for your specific application. Ensure the collation is set correctly; as well as any other settings that must be set during database creation. We'll create the database tables from your existing database.
For an operation with operands from the same character set but that mix a _bin collation and a _ci or _cs collation, the _bin collation is used. This is similar to how operations that mix nonbinary and binary strings evaluate the operands as binary strings, except that it is for collations rather than data types.
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 Here's the biggest hint to your problem:
Msg 5030, Level 16, State 2, Line 1 The database could not be exclusively locked to perform the operation.
What you need to do is set the database to single-user mode before you run the ALTER DATABASE statement, and then set it back to multi-user mode when it's completed. This will lock the database and make it available only to the current connection, which will allow you to successfully run the ALTER DATABASE ... COLLATE statement.
You can use SQL Server Management Studio or T-SQL commands to do this.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
