I am attempting to gain some knowledge and use cases on SSDT Database development and deployments and strugling with some deployment issues.
Specifically when using nested views. For some unknown reason when attempting to deploy / publish the files in the project to a local / live db it seems to mess up the references in the views.
In this project i have the following views (example):
View1 references View2 and View3 is referencing View1.
Building the project works fine. hoewever when i try to publish the database either by generating a dacpac by snapshot and publish it to the database or let Visual Studio generate an update script after (or not) comparing schemas i end up with an update script which tries to create the views in what seems to be the logical order in which they are stored in the project.
In this case View1 -> View2 -> View3. This means the publish fails because of reference issues. It can't create a view if the referenced view does not exist.
I have tried several options by adding the dacpac as reference in the project in various ways (same database, Same Server different Database w/ w/o database parameter) but in many cases i end up with a sql71561 / SQL71508 error which was another PITA to solve.
Online i can't find any good sources which explains how to work around this issue or which explains how this works properly.
Hopefully i can get some help here. If you need extra input from my side or want me to try something let me know.
Issue has been resolved by new insights. When trying to build the demo code to share with SO community i accidentally found the solution because i needed to clean up sensitive data(model) parts. Please let me elaborate on what was the issue.
The solution can be divided into two solutions:
I'll share some insights on the both matters.
Configuration of Database Project / Solution
The Visual Studio solution contained a single project in which all views were placed. The actual tables and other database items were separated in different Solutions / Project.
Solution1
Project1
View1
View2
View3
Solution2
Project1
Tables
Security
Schemas
Etc...
The views itself contained three-part identifiers [Database].[Schema].[Table/View]. This was both on the items inside the project (views) and on the items outside the project (tables etc.).
By just using that one separate Project with just the views led to missing references. It was not able to find the other views nor the tables (further see references).
One solution to this issue was making sure both the views and the tables refenced are in the same Solution / Project. Even with using three-part identifiers Visual Studio ignores these because of the existence of all items in the same project / solution. It will detect the dependencies this way.
the way references work
The other way to solve it was using references the right way in visual studio. which is the second possible solution.
Considering the earlier example where the views were in a different solution as the other elements led to missing references. However adding a dacpac as a database reference with the setting Same Database led to conflicting references and SQL71508 element already exists in the model. This is true because it exists in the references dacpac and we try to create a new view with the same name referencing itself in the dacpac. This is because it sees the three-part reference as a variable for the dacpac.
When using the dacpac setting for same Server, Different Database it resolves the mixed up references because it sees the three-part identifiers as an external reference and thinks that you creating a local copy of a view which is looking at the external dacpac. in other words it will not detect the nested view because it thinks you referencing a separate database not inside the project.
When building the project this will not lead to errors and deployment will work. however since it thinks you are referencing an external data source (in the form of a dacpac) it does not see the reference to the other local views.
The solution to this (atleast this worked for us) is to use two-part identifiers in our views when we need a local reference to the other views. This way it will look at other files inside the project instead of the referenced dacpac.
Since it will detect the reference to the other local views it will build correctly and detect the dependancies in the views inside the local project. It will then create a good build order for all views.
I guess you could also assign a different Variable name to the referenced Dacpac, use three-part-identifiers all the way but change the ones in the external dacpac to use the newly assigned variable name. We have not tested this (but i will when i get back home tonight).
So in all this was a good learning experience in how Database references work inside Database projects when using partial projects or when you have split up the database into several projects / solutions. Now to understand this Pandora's black box and convert them into a future-proof solution :)