Search code examples
sql-servervisual-studio-2015sql-server-data-tools

SSDT Changing Target Platforms problematic

I have a SSDT project with 3 data projects where, Project A, Project AB and Project ABC where:-

  • Project AB has a database reference to Project A
  • Project ABC has a database reference to Project AB and Project A

I'm deploying database project ABC locally to 2014 and then (after dev) deploying it to Azure.

All is well until I try changing the Target platform. Most of the time I can deploy to azure by just changing the target platform for just ABC and deploying, but occasionally I find I need to also change the target platform for Project A and/or Project AB to get it to generate a script against a 2014 database.

After doing so when I attempt to change it back to "Microsoft Azure SQL Database V12" when I attempt to generate a script it fails with the message in the Data Tools Operations:-

A project which specifies SQL Server 2014 as the target platform cannot be published to Microsoft Azure SQL Database v12

However, I definitely have all the target platforms set to Microsoft Azure SQL Database v12. I can also see on Project AB the reference to project A has warning icon enter image description here.

If I attempt to delete the database reference and re-add the reference (a same database project reference), after successfully adding the reference I get a dialog...

enter image description here

What output directory?! I've tried deleting the bin and obj folders, without success.

The only work around I've found is to wipe everything (without reverting uncommitted changes), after pulling everything from source control I can publish and generate a script against azure again. So I must be missing something.

Has anyone encountered these issues and found an easier way to work around them?

Solution

  • I've found the work around for this issue is to always ensure when you compile the projects all the target platforms are set to the same Target Platform, in my case Azure SQL Database V12. If you wish to deploy to a local SQL Server database, after ensuring all the referenced projects have been successfully built against the same platform (i.e. for me Azure SQL DB V12), switch the Target Platform for Project you're deploying to SQL Server 2014 (or whatever version your local DB is) then deploy, then switch it back again before you compile any new changes.

    I've found you can end up with issues such as the thing refusing to deploy or schema changes in referenced database projects not being deployed unless you compile with all the Target Platforms set to the same platform. E.g in the above example if the target platform for project ABC is not the same as project A, you can make changes in project A, compile and deploy ABC successfully, but none of the changes made in project A will be deployed.

    EDIT / UPDATE: I've found that this work around is no longer required if you're deploying to SQL Server 2017 (aka V14.0.* ) from VS 2017. You can deploy a project targeting azure to a local SQL Server 2017 instance without the issues described in this question. I've not tried deploying to other versions of SQL Server, so I don't know if it's a change to SQL Server or a change to Visual Studio, in any case it's now working without need of any tricks.