Schema compare - Unexpected exception caught during population of source model: Object reference not set to instance object

Question

I've been running a schema compare in a database project in Visual Studio 2017, when I do this I get the following message in the Error List tab:

Unexpected exception caught during population of source model: Object reference not set to instance object.

I found this blog, which appears to be the same issue, but the suggested solution (of removing the entry using the Select Target Schema window) has not worked despite trying it a few times.

The compare has (and does) work fine with the same project and database in Visual Studio 2013, so I have a work around, but it would be nice to know what is causing the problem (and leave VS2013 behind!)

Zygapophysis

Answer 1

I found a solution to this; for database projects there is a 'Target platform' setting in the properties (see below). I set this to SQL Server 2017 and the compare now works.

The default platform required appears to depend on the compatibility of the database (see https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level); while I required 2017 when I initially encountered this problem, a recurrence (against a database with a compatibility level of 120) needed SQL Server 2014 to be selected.

Oddly I have now seen that just switching the target platform back and forth can solve the problem e.g. I have a database project with SQL commands which were not present in SQL 2014, I ran the compare with a target of 2017 and it failed with the above error, ran with a 2014 target and it errors (as you would expect, since it does not understand the newer SQL functions), switch back to the 2017 target and the compare now works fine!

Edit: different job, different DB version (2019). Has all been working fine for months then this error cropped up. the above didn't work this time, so just in case anyone finds the same, the tried and tested closing and reopening VS sorted it!

Zygapophysis

Be sure to close and re-open your Schema Compare after saving the Target platform change. – Sieracki

It worked for azure database. I set to other variant and it's work. – Radioactive

And this is why my youngest child is named d219. – Pinnacle

Answer 2

I was having this issue too and none of the other suggestions worked for me. Eventually I realized that it was because the selected configuration in Visual Studio was set to something other than Debug. Changing that drop-down back to Debug got things working again.

Cuprous