Database project: database references do not work

Question

In a database project DB2 has references to DB1. When I make a dacpac of DB1 and add a reference to the dacpac in DB2, then the project compiles without error.

Since DB1 and DB2 are in the same solution anyhow, I am trying to add a database reference instead of the reference to the dacpac. Since DB1 and DB2 may reside on different servers with different names in production, I added $(parameters) for databases and servers. The project does not compile:

Error 1069 SQL71561: View: xyz has an unresolved reference to object [$(server)].[$(dbname)].[someschema].[sometable].

The parameters are set correctly. Did I overlook something concerning database references?

Answer 1

First just to verify that the projects themselves are setup correctly. You have a project in the solution for DB1. You have a project in the solution for DB2.

To start with a clean slate (often easiest with database references - you cannot edit properties easily after the reference has been added). Remove any existing database references to DB1 from DB2 project.

Do a clean of the solution. Build DB1 (Verify that a DACPAC has been created for DB1 in \bin\debug etc) and fix any errors if necessary.

In DB2 add a database reference to a solution item. Right click on References, select Add Database Reference. In the dropdown choose the first item (Database project in the current solution). Choose DB1.

At the bottom, select 'Different Database, Different Server' from the dropdown. Enter the variables again. Verify that the example usage at the bottom indicates 'SELECT * FROM [$(server)].[$(dbname)].[Schema1].[Table1]' (Or similar depending on your choice of variable names). Take note of that sample and copy that to a note or clip somewhere.

That should resolve the issues. You have already changed the views \ procs to reference the variables.

I think the easiest rule here is if you aren't sure, delete the reference and add it again. There is no easy way to edit variable names etc. In addition, if DB1 build fails, then you will still get reference errors like above.

Answer 2

I hadn't noticed that the referenced database projects in my solution did not compile in Visual Studio because of compile errors. Once the referenced projects compiled, the reference to those projects also worked.

Being able to extract a dacpac in Visual Studio is no indication for the circumstance that the project compiles; when extracting a dacpac directly from a database the external references of that database will be valid while the database project must be provided with those references first.