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!)

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!