SSMS fails to display database properties - cannot resolve collation conflict UTF-8 and Ascii

Question

The server collation is Latin1_General_CI_AS, the database collation is Latin1_General_100_CI_AS_SC_UTF8

right-click/properties (SSMS 18.9.2) on database in SQL Server (15.0.4063.15) returns:

TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot resolve the collation conflict between "Latin1_General_100_CI_AI_SC_UTF8" and "Latin1_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Latin1_General_100_CI_AI_SC_UTF8" and "Latin1_General_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)
For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-468-database-engine-error

Answer 1

digging deeper.... ok let's forget about UTF8, that's just a red herring.

Alter the collation of the DB on the Primary
Right-click/Properties on the secondary and it fails

Cannot show requested dialog.
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)

Answer 2

I was not able to repro this (with a server collation of Finnish_Swedish_100_CS_AS_SC_UTF8 and a database collation of Greek_CI_AS.)

While it is a bit of work, you use Profiler to track down the problematic query. Include the events RPC:Starting, RPC:Completed, SQL:BatchStarting, SQL:BatchCompleted and Error:Exception to see where this happens.

Answer 3

Hi @Burch, Adrian ,

Do you have to use such a collation(Latin1_General_100_CI_AS_SC_UTF8)?
If not, you can try to modify the collation of the database.
The statement is as follows:
USE [master]
GO
ALTER DATABASE [YourDatabase] COLLATE Latin1_General_CI_AS
GO

Note: Don't easily want to modify the collation of the server, which requires reinstalling SQL Server.

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Good day @Burch, Adrian

The only thing that come to my mind right now, is that your query (when you use the properties windows) for some strange reason uses tempdb for the task. In this case, the issue makes sense since tempdb has the same COLLATE as the server and this can lead to a conflict.

Do you have issue with resources that might led to use of tempdb?

----------

I tried without succeed to reproduce it using the following configuration (no issue in my case):

Server instance :                         SQL_Latin1_General_CP1_CI_AS  
Database :                                   Latin1_General_100_CI_AS_SC_UTF8  
SQL Server Management Studio 15.0.18386.0  

Cannot resolve the collation conflict between "Latin1_General_100_CI_AI_SC_UTF8" and "Latin1_General_CI_AS" in the equal to operation.

Did you made a mistake in the post? I notice that the error COLLATE using "AI" and your database according to the description is using "AS"

Answer 5

that's interesting, this morning it is working just fine.... I wonder if it was as simple as restarting SSMS, quite possibly

Thank you for all your responses.