Database Migration Assistant fails with error 'Cannot resolve the collation conflict'

Question

I have tried to run an assessment using DMA but I keep getting the following error:

An error occurred while attempting to reverse engineer elements of type Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlCredential: Batch Command during reverse engineer failed with Error Code: -2146232060 Line Number: 40 Error Message: Cannot resolve the collation conflict between "Danish_Norwegian_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation..

Cannot resolve the collation conflict between "Danish_Norwegian_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

After searching, I found out that the error is a dacFx issue which I am not familiar with. I run tests using SSMS and Visual Studio to generate a dacpac but I got the same error.

Is there a solution or work around? I only want a DB assessment using DMA.

The version of the instance: Microsoft SQL Server 2017 (RTM-CU18) (KB4527377)

Answer 1

Hi @James-09 ,

running a Profiler trace with SP:StmtCompleted and Error:Exception shows the exception is on the master database but it doesn't show which object (or I can't find >which object!).

Try code as next to find out the object firstly:

SELECT col.name, col.collation_name,object_id FROM sys.columns col WHERE col.collation_name = 'Danish_Norwegian_CI_AS'  

And then change it to 'SQL_Latin1_General_CP1_CI_AS' as this : set-or-change-the-column-collation

More information: view-collation-information, changing-sql-server-collation-after-installation,

I am not sure why DMA would look into the master DB when it's supposed to do an assessment of another DB!

Please make sure you have selected the destination dbs on your side, DMA only assesses the choosed ones. I test on my side, it works fine.

Follow steps as this to test again: an-overview-of-sql-server-database-migration-tools-provided-by-microsoft

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

It is obviously a bug in DacFx (which neither I am very familiar with).

I could possibly think of running a Profiler trace with SP:StmtCompleted and Error:Exception included to see which statement that fails. Maybe is possible to change a collation somewhere to get around the error. But I would not really be holding my breath.

Answer 3

I changed the server collation and that solved the problem.

The error is not a bug in DMA but I think in dacFx framework as I couldn't even generate a dacpac file before. It would of course have been better if DMA worked around this issue without having to do any changes on the server.

Thank you both for your help :)