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:

Collation conflict error message

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.

  • what collation is the msdb database? Latin1_General_BIN? – Max Vernon Mar 31 '16 at 17:15
  • 1
    Yes, msdb is Latin1_General_BIN. – YaHozna Apr 1 '16 at 8:56

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.

  • 1
    Overall correct (+1) but there are two short-term fixes that would work ;). I noted them in my answer, but also said that your suggestion to recreate MSDB was the long-term solution. – Solomon Rutzky Mar 31 '16 at 19:33
  • 1
    Many thanks Paul. I've been told by the person who did the migration that msdb wasn't migrated, but I do have my doubts :) – YaHozna Apr 1 '16 at 8:49
  • @YaHozna If sys.databases reports that master is Latin1_General_CI_AS and msdb is Latin1_General_BIN, then there is nothing to be uncertain of: msdb had to be migrated since it is not possible to change the collation of the system DBs. – Solomon Rutzky Apr 1 '16 at 18:49
  • @YaHozna Also, it was Max who posted this answer, not Paul ;-) – Solomon Rutzky Apr 1 '16 at 18:49

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.

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.

  • +1'd - Interesting answer, but wouldn't altering a system view put you at risk of not being supported by Microsoft? – Max Vernon Mar 31 '16 at 20:02
  • @MaxVernon I guess it depends on what needs supporting. While those two objects are is_ms_shipped = 1, sysdac_instances_internal is a User Table, not a System Table, so I would assume the same for the View. As I said, certainly not ideal, but much quicker than rebuilding msdb so at least a means of moving forward. Also, I don't see any harm in altering the View since it doesn't affect how other stuff works, it merely allows this to work. – Solomon Rutzky Mar 31 '16 at 20:17
  • agreed. I'd alter the view myself, and just move on. – Max Vernon Mar 31 '16 at 20:18
  • Many thanks all for the input. Altering the view would seem to be the easiest and safest option here. Gordon. – YaHozna Apr 1 '16 at 9:00

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.

  • How does this apply to a system view, whose definition cannot be altered? – Max Vernon Mar 31 '16 at 17:28
  • @MaxVernon - I understood the CATALOG_DEFAULT was used to overcome the metadata collation problem. Appreciate any clarification you have. But I certainly agree that having the same collation is the best answer. – RLF Mar 31 '16 at 17:41
  • I've certainly managed to amend a couple of queries that were throwing the same collation conflicts by using COLLATE DEFAULT_DATABASE however I was uncertain about amending a system view, although srutzky has mentioned that below as being the lesser of the evils. – YaHozna Apr 1 '16 at 8:59
  • @YaHozna While DEFAULT_DATABASE is a very handy option in many cases, I would not recommend it for your situation. The msdb database has a binary collation while the server (i.e. other system DBs) have a case-insensitive Collation. By using DEFAULT_DATABASE you are making the operation use the binary Collation while most other operations against server meta-data will be case-insensitive. I would think it better to prefer the server's default Collation, which is now Latin1_General_CI_AS. – Solomon Rutzky Apr 1 '16 at 20:05

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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