If we are working with an SQL Server project in Visual Studio then one of the most common tasks is to first import the scheme of the already existing database into this project. It works pretty well but the real issue will first be discovered when trying to build the project because all unresolved references (objects from other databases, linked servers, etc.) are reported as errors and we can´t deploy the project without resolving them first. I will show you how to solve this problem.
Step one, open the new SQL Server project and create one database called MainDatabase. In this database, create a view with the name [dbo].[ViewWithOuterDbReference] containing only one statement: SELECT * FROM [RererencedDatabase].[dbo].[Referencedtable]. This database doesn’t exist yet and that’s the reason why this object identifier is marked with a red line. Unresolved references are marked in the same way if we will import the existing database scheme.
Try building the project now. The result will be as seen in this picture:
Build engine did his job and discovered an unresolved reference and building failed because this reference can later cause later our queries to fail when referencing this view.
In our sample project, we need to take the following steps to build the project successfully.
Create another database in the same solution with the name [ReferencedDatabase]. Inside this database, create a new table called [ReferencedTable]. Now, we have something we can reference as [RererencedDatabase].[dbo].[Referencedtable] in queries from other databases.
However, if we will try building the project now, the same error will be fired because of our view [dbo].[ViewWithOuterDbReference] in [MainDatabase] still doesn’t know that the new database is there and the reference error cannot be solved. To move forward we need to explicitly set up a new database reference between our database.
Go to the References folder of [MainDatabase], right-click and select Add Database Reference:
The following dialog will pop up and we select the Database Reference as per the green box below:
Please note the Database Location section and Database variable field value. Go back to it.
After pressing OK, we can see a new item in the References folder saying, that our [MainDatabase] now has a new reference to [ReferencedDatabase]:
However, we are still not finished building will fail again. We have set the reference but next, we should adjust our code so that the build engine can use this new reference to solve existing reference conflicts.
By right-clicking on the new reference created above we will display its properties and copy the Database Variable Name property value to the clipboard:
Then, go back to the view and replace the [ReferencedDatabase] identifier with the [$(ReferencedDatabase)] variable from the clipboard. Shortly after that, a red line warning is removed from the code and we can build the project. All unresolved references have now been solved.
Further reading: