SSDT in VS 2022 not finding reference errors.

Problem Description：
I created a database project in visual studio 2022, when moving the temp table join below the reference errors, the build suddenly fails.

Questions:
Why failed to compile the project, after moving the temp table join below the reference errors.
Please help to check for complier issue in VS and SSDT.

I have attached database project and reproduce records in here.
[Bi-DB project and records.zip](https://github.com/user-attachments/files/17198822/Bi-DB.project.and.records.zip)

Error Message：
Severity Code Description Project File Line Suppression State

Error SQL71501: Procedure: [dbo]. [usp_Merge_Salesforce_PipelineRevenue] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [$(Fix_OLAP)]. [dbo]. [vw_GeotaxonomyByAreaRegion]. [Geo], [$(Fix_OLAP)]. [dbo]. [vw_GeotaxonomyByAreaRegion]. [newRegion]::[Geo] or [dbo]. [Salesforce_Opportunity]. [newRegion]::[Geo]. Fix_tmp C:\Users\caryhsu\CaseBuddy.CaseData\2407230030008031_Dan\2024-07-23\Bi-DB\Bi-DB\Fix_tmp\dbo\Stored Procedures\usp_Merge_Salesforce_PipelineRevenue.sql 18

Steps to reproduce the problem or scenario:

Download the database project in SSDT(I have open this project in SSDT 2022),you can succssfully to complier the project.
You can try to change the code shows as below, then complier it again, you will get following error message.
Change code from Fix_tmp -> Stored Procedures -> usp_Merge_Salesfore_PipelineRevenue.sql

before:

SELECT
*
FROM Salesforce_Opportunity AS opp
LEFT JOIN #Reset AS cte
ON cte. OpportunityId = opp. Id
LEFT JOIN [$(Fix_OLAP)]… vw_GeotaxonomyByAreaRegion AS newRegion
ON (
opp. Owner_Geo__c = newRegion.Geo
AND opp. Owner_Area__c = newRegion.Area
AND opp. Owner_Region__c = newRegion.Region
) OR (
opp. Owner_Geo__c = ‘Moosend’
AND newRegion.Region = ‘Moosend’
) OR (
opp. Owner_Geo__c = ‘Reflektion’
AND newRegion.Region = ‘Reflektion’
)

After:

SELECT
*
FROM Salesforce_Opportunity AS opp
LEFT JOIN [$(Fix_OLAP)]… vw_GeotaxonomyByAreaRegion AS newRegion
ON (
opp. Owner_Geo__c = newRegion.Geo
AND opp. Owner_Area__c = newRegion.Area
AND opp. Owner_Region__c = newRegion.Region
) OR (
opp. Owner_Geo__c = ‘Moosend’
AND newRegion.Region = ‘Moosend’
) OR (
opp. Owner_Geo__c = ‘Reflektion’
AND newRegion.Region = ‘Reflektion’
)
LEFT JOIN #Reset AS cte
ON cte. OpportunityId = opp. Id

Troubleshooting：

1. I can easy and always reproduce this issue from the project.

2. Target framework is “.net framework 4.7.1”, I have try to change the framework to .net 4.8.1, but got the same issue.

3. I can see a SQLCMD variables defined in Fix_tmp project shows as below, the variable defines and uses in SQLCMD mode, when I try to remove and change code show as below, I can pass the complier when move the temp table after or before the join.


4. We tried can enable the code analysis on build to show more information when build the solution, but it seems to me that this issue related to VS complier.

Start with the attached Bi-DB project and records, then inspect Fix_tmp\dbo\Stored Procedures\usp_Merge_Salesforce_PipelineRevenue.sql. Reproduce the build after moving the #Reset join below the vw_GeotaxonomyByAreaRegion join and compare it with the original ordering and SQLCMD variable configuration. Done means determining whether the unresolved SQL71501 reference is a compiler defect and documenting a verified workaround or fix.

Written by the indexing model from the issue text.