2

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?

  • Run SQL Server Profiler and then try to see the database properties to find out the command management studio is executing against SQL Server – shilan May 30 at 11:30
  • I don't think you can (or need to) change the collation of the connection, you need to resolve a collation conflict between two databases for something that SSMS is trying to enumerate when gathering properties. – Aaron Bertrand May 30 at 11:48
2

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

  • Do you know some workaround to avoid databases collation change? – Rubén Pozo May 30 at 12:45
  • 1
    @RubénPozo Yes, as I mentioned in my answer here, Option 1 in my other answer (meaning, doing the ALTER VIEW using SQL_Latin1_General_CP1_CS_AS) would be the easiest / least-impacting approach. – Solomon Rutzky May 30 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.