Collation conflict ... Could not use dbo.sysdac_instances

After a migration of databases to a new 2014 server with default collation Latin1_General_CI_AS, but where most of the databases have a collation of Latin1_General_BIN, an attempt to import an Excel spreadsheet is throwing the following error:

I've tracked sysdac_instances down to msdb where it is a system view and querying it directly gives the same error. I'm hoping someone will be able to point me in the direction of a straighforward-ish solution to this problem?

Curiously though, running the import via SSMS 2008 works.

The sysdac_instances view is defined as follows:

CREATE VIEW [dbo].[sysdac_instances]
AS
    SELECT
        -- this must be locked down because we use instance_id visability as a security gate
        case 
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.instance_id
            when sd.owner_sid = SUSER_SID() then dac_instances.instance_id
            else NULL
        end as instance_id,
        dac_instances.instance_name,
        dac_instances.type_name,
        dac_instances.type_version,
        dac_instances.description,
        case 
            when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.type_stream
            when sd.owner_sid = SUSER_SID() then dac_instances.type_stream
            else NULL
        end as type_stream,
        dac_instances.date_created,
        dac_instances.created_by,
        dac_instances.instance_name as database_name
    FROM sysdac_instances_internal dac_instances
    LEFT JOIN sys.databases sd
        ON dac_instances.instance_name = sd.name

The view performs a join on instance_name against the sys.databases.name column. Since the column definition for the sysdac_instances_internal.instance_name does not specify a collation, it will assume the collation of the msdb database.

Perhaps you restored msdb from an instance where the collation was Latin1_General_BIN, which would cause the collation conflict. If that is the case, you'd need to recreate the MSDB database, since its collation cannot be altered.

StackExchange Database Administrators Q#133981, answer score: 7