Collation conflict ... Could not use dbo.sysdac_instances

Question

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.

Answer 1

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.

Answer 2

While @Max is correct about a) the root cause of this error (collation of restored msdb database does not match the server's default collation, which is used to set the collation of the name field in master.sys.sysdbreg -- the actual source of the name field in sys.databases), and b) that ideally you would rebuild msdb to have a collation matching the server's default (i.e. Latin1_General_CI_AS), there are two short-term solutions to get your import working until you can implement that ideal solution (and yes, I tried both and they do work :-):

1. Alter the definition of the dbo.sysdac_instances View to explicitly state the Collation which will bypass the conflict due to default collation precedence behavior:

   ALTER 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 COLLATE Latin1_General_CI_AS
   

   I pulled that definition straight from the DB using SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.sysdac_instances')); and then just added the COLLATE clause at the end.

   I used the Latin1_General_CI_AS Collation in the COLLATE clause to add in, rather than the binary Collation, so that this operation (i.e. matching on database names) would behave like any other comparison to sys.databases.name (that was, of course, not overriden using a COLLATE clause ;-)

2. Alter the collation of the field in question:

   USE [msdb];
   
   ALTER TABLE dbo.[sysdac_instances_internal]
         DROP CONSTRAINT [UQ_sysdac_instances_internal];
   
   ALTER TABLE dbo.[sysdac_instances_internal]
         ALTER COLUMN [instance_name] sysname COLLATE Latin1_General_CI_AS NOT NULL;
   
   ALTER TABLE dbo.[sysdac_instances_internal]
         ADD CONSTRAINT [UQ_sysdac_instances_internal]
         UNIQUE NONCLUSTERED ([instance_name] ASC);
   

Between the two options, #1 (i.e. altering the View) would have the least chance of adversely affecting other stuff within the msdb database using the underlying table.

If you are considering updating the collation of the instance and database(s), then please see the following post of mine:

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

Other notes:

• I am guessing that the import worked in SSMS 2008 most likely due to its code not referencing this particular View. I can't think of any other difference that could explain that behavior.

• When using binary collations, it is best to use the newer BIN2 collations and not the deprecated BIN collations.

• When possible, it is best to use the newest version of a particular Collation, and picking a variation of it that ends in _SC, if one is available. In the case of your new server, a better choice would have been: Latin1_General_100_CI_AS_SC.

• 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

Answer 3

In general collations can be persuaded to match one another. See my comments at:
Contained DB Collation error

Perhaps the comments on collation, particulary the CATALOG_DEFAULT, may provide you some assistance:

• The database collation is retained, but is only used as the default collation for user data.
• A new keyword, CATALOG_DEFAULT, is available in the COLLATE clause. This is used as a shortcut to the current collation of metadata in both contained and non-contained databases.

For more straightforward collation problems, such as between two databases, a sample query from that discussion is:

select NameValue COLLATE DATABASE_DEFAULT from MyDatabase.Schema.Table
EXCEPT
select NameValue COLLATE DATABASE_DEFAULT from TheirDatabase.Schema.Table

The value of this approach is that you do not need to specify a particular collation, but COLLATE DATABASE_DEFAULT allows you to use the collation of the current database. This would resolve data collation issues.