Sql-server – Error: 468 trying to connect to SQL server with SQL Server management studio

collationsql serversql server 2014ssms

I'm trying to connect to SQL Server 12 with SQL Server management studio 2014.

The connection is succesful, but when I try to see properties of a specific database with mouse right button I get this error:

No se puede resolver el conflicto de intercalación entre
"SQL_Latin1_General_CP1_CS_AS" y "Modern_Spanish_CI_AS" de la
operación equal to. No se pudo usar la vista o función
'dbo.sysdac_instances' debido a errores de enlace. (Microsoft SQL
Server, Error: 468)

It seems to be a problem related with collation.
How I could specify the proper collation for the connection?

Best Answer

How I could specify the proper collation for the connection?

You can't. And even if you could, that wouldn't help here since the problem is a conflict between two different string columns. The only aspect of this problem that is specific to SSMS is that SSMS is selecting from a specific system view — msdb.dbo.sysdac_instances — that is joining on these two string columns, expecting that they will always be using the same collation (and in most cases they do).

Please see my answer to this related / duplicate question:

Collation conflict … Could not use dbo.sysdac_instances

There I explain why this is happening and what your options are. This should be caused by having a different instance-level collation than the msdb collation. This can happen when restoring / attaching msdb created in another instance that had a different instance-level collation.

The option that should have the least amount of impact would be updating the definition of the dbo.sysdac_instances system view. Not ideal, but given the other options, it is also the easiest to reverse should it prove to be a problem, though I don't see how it could be a problem. In that example I used Latin1_General_CI_AS for the fix as it was the instance-level collation. In this case, I would use SQL_Latin1_General_CP1_CS_AS as I am guessing it is your instance-level default collation.

Please also see the following post of mine if you are considering updating the collation of the instance and database(s):

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Also, this is another example of why we need to have a special collation similar to DATABASE_DEFAULT but working at the instance level — INSTANCE_DEFAULT — so that there can be a generic solution to this type of issue. This would allow the msdb.dbo.sysdac_instances_internal table to be created using:

...
[instance_name]   sysname COLLATE INSTANCE_DEFAULT,
...

Or, it could be that the msdb.dbo.sysdac_instances view has this at the end:

LEFT JOIN sys.databases sd
    ON dac_instances.instance_name = sd.name COLLATE INSTANCE_DEFAULT

So please vote for the following suggestion:

Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation

Related Question