I am experiencing an unresolved reference error when I try to build my project because my project contains views that reference another database on the same server.
When I try to add a database reference, I am forced to select either a Data-tier Application (.dacpac), a System Database, or database project in the current solution.
Why I can't use these options:
System Database doesn't work → The external database is a user database, not master, msdb, model, or tempdb, so this option isn’t valid.
I don't want to add the database to the solution → This database is managed separately, and including it in the repo would mean maintaining an entire additional project that isn’t needed.
.dacpac isn't practical → The referenced database changes frequently, and keeping the .dacpac updated in the AzDO repo would require constant manual updates.
I am experiencing an unresolved reference error when I try to build my project because my project contains views that reference another database on the same server.
When I try to add a database reference, I am forced to select either a Data-tier Application (.dacpac), a System Database, or database project in the current solution.
Why I can't use these options:
System Database doesn't work → The external database is a user database, not master, msdb, model, or tempdb, so this option isn’t valid.
I don't want to add the database to the solution → This database is managed separately, and including it in the repo would mean maintaining an entire additional project that isn’t needed.
.dacpac isn't practical → The referenced database changes frequently, and keeping the .dacpac updated in the AzDO repo would require constant manual updates.
2 Answers
Reset to default 3The only way I know to do this is with including a dacpac for the other database. If it changes frequently, you could probably set up something on the post-build to automate copying it to the db project in which you're working, but unless the objects you're referencing change frequently, you really only need to update that file if the objects referenced change. For many cases, the core objects being referenced are likely pretty static so you can work on just pulling the latest dacpac when you need it or when something changes.
You must have reference. System database will surely not work, so you should either use another project or dacpac as the reference. As you are referencing objects in your project, then you know all needed objects and/or columns. Just create dummy project and add the only objects you use in your project to fix all reference problems. However as you mentioned that you are using them in the view already, I assume that you are using 3 or 4 part names there. It will not work in SSDT. I would suggest to create synonyms for all the external objects you are using and use them instead. Synonyms support 3/4 part name references.
.dacpac isn't practicalthat's how it only works – IVNSTN Commented Feb 13 at 8:40