Fix "Unresolved Reference" errors using sys.columns in a different database

69 Views Asked by At

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 OtherDb project in my MainDb project
  • A reference to the master database in my MainDb project
  • A database reference to the master database in my OtherDb project
    • I'm able to write views in that project that reference the local sys.columns just fine.

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?

0

There are 0 best solutions below