SSDT has an unresolved reference to object for views but works fine for procedure

Question

This error is not the "normal error", when there is a missing database reference. I have a database reference added correctly, and it worked fine so far with stored procedures.

The problem:

I have a database project, let's call it BIDK. This database has a reference to dacpac file, build from a nother database project called RPTDK.

RPTDK has a table called dbo.BILLINGITEM, which I can reference from BIDK database project using this code:

create proc CommonDM.TestReferenceFromProc
as
select BILLINGITEM_ID,
       TIMESTAMP,
       BILLINGITEMTYPE_ENUMID,
       VATCODE_ENUMID,
       LCOMPANY_ID,
       LEASESERVICECOMPONENT_ID
from [RPTDK].[dbo].BILLINGITEM as bilitm;

This works fine, and I can build the project BIDK. Shiny Happy Poeple Dancing!

However, if I add this view, then the build fails:

create view CommonDM.TestReferenceFromView
as
select BILLINGITEM_ID,
       TIMESTAMP,
       BILLINGITEMTYPE_ENUMID,
       VATCODE_ENUMID,
       LCOMPANY_ID,
       LEASESERVICECOMPONENT_ID
from [RPTDK].[dbo].BILLINGITEM as bilitm

I cannot build the project BIDK with the view added, and I get the error message:

[CommonDM].[TestReferenceFromView] has an unresolved reference to object [RPTDK].[dbo].[BILLINGITEM]

Any idea why this is happening?

Why is it working for stored proc and not for views?

Answer 1

After testing and trying different settings, I can conclude that the "correct" approach is to use the [$(RPTDK)] syntax, where the RPTDK is the name that of the parameter, as it is set when one adds the database reference to begin with.

select BILLINGITEM_ID,
       TIMESTAMP,
       BILLINGITEMTYPE_ENUMID,
       VATCODE_ENUMID,
       LCOMPANY_ID,
       LEASESERVICECOMPONENT_ID
from [$(RPTDK)].[dbo].BILLINGITEM as bilitm

It is a bit annoying, and the code is now dependent on a SQLCMD mode parameter to work, when you want to copy and paste into a SSMS window.

Thanks Alfin for the assistance.

ps

The reason why this is not failing in a stored proc, is that it seems to be that Visual Studio SSDT is not checking for referenced objects in a stored proc, but does it for views. I tried to remove the reference all togetger, and the stored proc code still managed to build succesfully.