Unable to compile SSDT Database Project with a view that has fully qualified name of table in view definition

Question

We have a SQL 2019 database where all table names are fully qualified in views starting with the database name. We do NOT have the option of avoiding the fully qualified reference as the view definition is auto-generated (otherwise I would simply not fully qualify them). When views are defined by referencing tables within the same database as the view, the SSDT project complains that it has an unresolved reference.

Visual Studio does not allow adding a database reference to itself. The only way I can get it to compile is to create a DACPAC of the same database and then add that as a reference along with removing the database variable ($Name).

Is there any other method of providing fully qualified table names in views without having to create a DACPAC in SSDT project?

Answer 1

You can't use 3-4 part naming in SSDT normally. You can workaround this by using variables in the code. So let's say, that you have [localhost].[reports].[dbo].[your_table] you'll need to use [$(ReportServer)].[$(ReportDatabase)].[dbo].[your_table].

Answer 2

Only way I'm aware of would be to take the view code out of the project and handle in post-deploy scripts. This is done by design, because the database name may not be what was defined in the original code.

Answer 3

I have a DacPac project containing objects which use three part naming to refer to the containing database (hundreds of instances such as [thisDb].[dbo].[obj]* exist). I need compare and update this database, but the db project fails to build due to 200+ sql71561 errors.

I did not want to remove the unnecessary database name part or switch to using a database name variable. To successfully build (or compare, and then update) a database using three part naming or fully qualified naming to refer to itself, there is a way I found to pacify visual studio. It's not what I'd prefer, but it works.

1. Create a copy of the original db project.
2. In the copy db project, update all local database object references to use just two part names ([dbo].[obj]) instead of three part names (I used find & replace).
3. Make sure the copy db project targets the same SQL server version and builds successfully.
4. Reference the copy db project from the original db project (whether via database variable, database name only, or dacpac).
5. The original db project can now build because its references can be resolved. You'll end up with a dacpac for both the original and the copy, but at least the errors are gone and it compiles.