I have an SSDT Solution with two database projects, let's call them MainDb and OtherDb. I'm trying to create a view on the MainDb database which surfaces some information about related objects in the OtherDB database. For simplicity, here's an example of the type of query I want in the view:
create view dbo.RelatedTables
as
select m.Name
from sys.tables m
where exists
(
select 1
from OtherDb.sys.tables o
where m.name = o.name
)
The problem is, I'm getting an error in my MainDb project saying:
Error SQL71561: View: [dbo].[RelatedTables] has an unresolved reference to object [OtherDb].[sys].[columns].
I have:
- A database reference to the
OtherDbproject in myMainDbproject - A reference to the
masterdatabase in myMainDbproject- I know you need a reference to the
masterdatabase in order to remove "unresolved reference" errors
- I know you need a reference to the
- A database reference to the
masterdatabase in myOtherDbproject- I'm able to write views in that project that reference the local
sys.columnsjust fine.
- I'm able to write views in that project that reference the local
Is anyone aware of how to configure an SSDT project to build when a view on one database has to reference a catalog view on a different database?