When using SQL Server Data Tools, what can I do about the error message 'An error has occurred. The target schema is in an unknown state.'?

Question

I'm comparing a database in a local SQL Server 2014 instance against an SSDT database project.

I have done previous comparisons and they have worked fine.

At times I also change the project database target from SQL Server 2014 to SQL Azure to push changes to a SQL Azure database.

I made sure the project was set to SQL Server 2014 before trying to perform the most recent comparison and update.

Unfortunately the error message gives no indication of what went wrong, where it went wrong, or where to find more information.

Further more a quick Google for the error text gives no results! It seems no one has even mentioned this error before on the internet.

(That's not quite true any more. You can now find where I'm asking about this issue on the MSDN forums, currently with no response.)

Does anyone know if this error message relates to something specific? Or is there a way to find out more information from a log? How do I debug, and ideally resolve this?!

Bugeye

Does the project build without any warnings or errors? – Moony

Yep, it was all clean. No warnings, nor errors. – Bugeye

Answer 1

For now I have deleted all the sql files, and the cache file (dbmdl) from the project and then performed another compare/update.

This allows me to continue working, but it's an unsatisfying resolution.

Bugeye

I don't know why but I had to delete the SQL files from within the project. It didn't work when I deleted them in the file explorer. – Clutch

Fixed it for me, delete the dbmdl, no need to purge the SQL files. – Quintero

Answer 2

I had this problem:

• Using SSDT 15.1 in Visual Studio 2017
• After performing a Schema Comparison which compared a localhost SQL Server 2016 database with an open SSDT project.
• When a Temporal Table in the localhost SQL Server had been renamed but the SSDT project had the old name.
  • There were many other changes to other normal tables, however - like added columns, new foreign-key constraints, etc.

I tried performing a Clean Project, deleting the obj and bin directories in the SSDT project, and reloading the solution - none worked.

I re-ran the comparison and deselected all changes detected in the Temporal Table and clicked Update to apply all of the other changes and this succeeded. I then clicked Compare again and this no-longer showed the renamed temporal table but did show the automatically defined index on the temporal table's history table (which was now correctly named in SSDT but not the localhost SQL Server). I manually renamed the index in the localhost SQL Server using SSMS (not SSDT) and re-ran the comparison and it detected no changes. Phew!

It looks like as of mid-2019 that the current build of SSDT still has some issues with Temporal Tables.

Involved

Answer 3

I experienced the same error message when was trying to compare DB (compatibility mode SQL Server 2012 (110)) to DB project (target platform SQL Server 2012). Following actions worked out for me:

• Clean DB project
• Set both DB and DB project to SQL Server 2014. Compare again, you'll have the same error
• Set both DB and DB project to SQL Server 2012. Compare again, no error message any more.

I used:

• Microsoft Visual Studio Enterprise 2015 Version 14.0.23107.0 D14REL
• SQL Server Data Tools 14.0.50730.0

Anting

Answer 4

I had the same error on Project Database 2008.

I converted all the database projects from .NET 4.5 to .NET 4.0 and it worked.

Citreous

Answer 5

I had received same problem while working on Visual Studio 17. I just closed the solution and visual studio and re-opened visual studio with "Run As Administrator" and this resolved the issue.

Crackbrain

Hi Supriya Kolate - Amble. Do you have pictures supporting that claim? Would be great to see them, but just if you have. – Broadsword

Answer 6

Same error, trying to update the db project. I had its Target platform set to SQL Server 2016 and Target framework set to .Net Framework 4.7.2.

What worked for me was to change the Target framework to .Net Framework 4, then compared and updated a few changes, and it worked. Then set the Target framework again to 4.7.2, compared and updated the rest of the changes. It's been working ever since.

Latten

Answer 7

For me this problem was being caused by a new schema that I had added to the database for testing. Dropping the schema resolved the issue for me.

Bremsstrahlung