Hi all!

I am migrating content from v7.15.3 to v8.6.1. I am encountering the following error:

{"@t":"2020-04-24T07:32:59.4884128Z","@mt":"Exception ({InstanceId}).","@l":"Error","@x":"System.Data.SqlClient.SqlException (0x80131904): Cannot resolve collation conflict between \"Danish_Norwegian_CI_AS\" and \"SQL_Latin1_General_CP1_CI_AS\" in CASE operator occurring in SELECT statement column 35. ...

(Error truncated for clarity)

I can see that it occurs at the following migration plan step:

PostMigration: Umbraco.Core.Migrations.PostMigrations.RebuildPublishedSnapshot.

It appears that the collation exception is being thrown for the following query:

SELECT [umbracoDocument].[nodeId] AS [NodeId], 
[umbracoDocument].[published] AS [Published], 
[umbracoDocument].[edited] AS [Edited] , 
[umbracoContent].[nodeId] AS [ContentDto__NodeId], 
[umbracoContent].[contentTypeId] AS [ContentDto__ContentTypeId] , 
[umbracoNode].[id] AS [ContentDto__NodeDto__NodeId], 
[umbracoNode].[uniqueId] AS [ContentDto__NodeDto__UniqueId], 
[umbracoNode].[parentId] AS [ContentDto__NodeDto__ParentId], 
[umbracoNode].[level] AS [ContentDto__NodeDto__Level], 
[umbracoNode].[path] AS [ContentDto__NodeDto__Path], 
[umbracoNode].[sortOrder] AS [ContentDto__NodeDto__SortOrder], 
[umbracoNode].[trashed] AS [ContentDto__NodeDto__Trashed], 
[umbracoNode].[nodeUser] AS [ContentDto__NodeDto__UserId], 
[umbracoNode].[text] AS [ContentDto__NodeDto__Text], 
[umbracoNode].[nodeObjectType] AS [ContentDto__NodeDto__NodeObjectType], 
[umbracoNode].[createDate] AS [ContentDto__NodeDto__CreateDate] , 
[umbracoDocumentVersion].[id] AS [DocumentVersionDto__Id], 
[umbracoDocumentVersion].[templateId] AS [DocumentVersionDto__TemplateId], 
[umbracoDocumentVersion].[published] AS [DocumentVersionDto__Published] , 
[umbracoContentVersion].[id] AS [DocumentVersionDto__ContentVersionDto__Id], 
[umbracoContentVersion].[nodeId] AS [DocumentVersionDto__ContentVersionDto__NodeId], 
[umbracoContentVersion].[versionDate] AS [DocumentVersionDto__ContentVersionDto__VersionDate], 
[umbracoContentVersion].[userId] AS [DocumentVersionDto__ContentVersionDto__UserId], 
[umbracoContentVersion].[current] AS [DocumentVersionDto__ContentVersionDto__Current], 
[umbracoContentVersion].[text] AS [DocumentVersionDto__ContentVersionDto__Text] , 
[pdv].[id] AS [PublishedVersionDto__Id], 
[pdv].[templateId] AS [PublishedVersionDto__TemplateId], 
[pdv].[published] AS [PublishedVersionDto__Published] , 
[pcv].[id] AS [PublishedVersionDto__ContentVersionDto__Id], 
[pcv].[nodeId] AS [PublishedVersionDto__ContentVersionDto__NodeId], 
[pcv].[versionDate] AS [PublishedVersionDto__ContentVersionDto__VersionDate], 
[pcv].[userId] AS [PublishedVersionDto__ContentVersionDto__UserId], 
[pcv].[current] AS [PublishedVersionDto__ContentVersionDto__Current], 
[pcv].[text] AS [PublishedVersionDto__ContentVersionDto__Text] , 
    COALESCE([ccv].[name],[umbracoNode].[text]) 
    AS variantName 
    FROM [umbracoDocument] 
    INNER JOIN [umbracoContent] 
    ON [umbracoDocument].[nodeId] = [umbracoContent].[nodeId] 
        INNER JOIN [umbracoNode] ON [umbracoContent].[nodeId] = [umbracoNode].[id] 
            INNER JOIN [umbracoContentVersion] ON ([umbracoDocument].[nodeId] = [umbracoContentVersion].[nodeId]) 
                INNER JOIN [umbracoDocumentVersion] ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id]) 
                    LEFT JOIN [umbracoContentVersion] [pcv] 
                        INNER JOIN [umbracoDocumentVersion] [pdv] ON (([pcv].[id] = [pdv].[id]) AND [pdv].[published] = @0) ON ([umbracoDocument].[nodeId] = [pcv].[nodeId]) 
                        LEFT JOIN [umbracoContentVersionCultureVariation] [ccv] 
                        INNER JOIN [umbracoLanguage] [lang] 
                            ON (([ccv].[languageId] = [lang].[id]) 
                            AND ([lang].[languageISOCode] = @1)) 
                            ON ([umbracoContentVersion].[id] = [ccv].[versionId]) 
                            WHERE (([umbracoNode].[nodeObjectType] = @2)) AND ([umbracoContentVersion].[current] = @3) 
                            ORDER BY [ContentDto__NodeDto__Path] , [ContentDto__NodeDto__NodeId]

I have extracted the above from the logs.

Now my idea was, I will take a look at the DB and see if there are any columns that from the tables involved in this query that have different collations than Danish_Norwegian_CI_AS and then try to unify those collations to try and resolve this, however, when using the query:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLLATION_NAME IS NOT NULL
    AND TABLE_NAME = 'umbracoNode'
    OR TABLE_NAME = 'umbracoDocument'
    OR TABLE_NAME = 'umbracoContent'
    OR TABLE_NAME = 'umbracoNode'
    OR TABLE_NAME = 'umbracoDocumentVersion'
    OR TABLE_NAME = 'umbracoContentVersion' 
    AND TABLE_NAME NOT LIKE '%uCommerce%'
    ORDER BY TABLE_NAME

to try and get the collation list I only see the following:

What is interesting is that I cannot find a lot of the tables that are in the query causing the error, perhaps they are created in the new DB to which the data is trying to migrate to?

I could use some ideas as to how to fix this, seems like it's one of the last steps to get me to a successful migration.

EDIT:

I noticed that in v8 the prefixes for some tables are renamed from cms* to umbraco*, so I have updated my collation query to this:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLLATION_NAME IS NOT NULL
    AND TABLE_NAME = 'umbracoNode'
    OR TABLE_NAME = 'cmsDocument'
    OR TABLE_NAME = 'cmsContent'
    OR TABLE_NAME = 'umbracoNode'
    OR TABLE_NAME = 'cmsDocumentVersion'
    OR TABLE_NAME = 'cmsContentVersion' 
    AND TABLE_NAME NOT LIKE '%uCommerce%'
    ORDER BY TABLE_NAME

And now I see more tables in the results, but still - majority have NULL collation and a few have Danish_Norwegian_CI_AS .

EDIT 2:

Can see that the collation of the database is SQL_Latin1_General_CP1_CI_AS .