DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
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.
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_Geoc = newRegion.Geo
AND opp. Owner_Area__c = newRegion.Area
AND opp. Owner_Regionc = 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_Geoc = newRegion.Geo
AND opp. Owner_Area__c = newRegion.Area
AND opp. Owner_Regionc = 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：
I can easy and always reproduce this issue from the project.
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.
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.
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.
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
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_Geoc = newRegion.Geo AND opp. Owner_Area__c = newRegion.Area AND opp. Owner_Regionc = 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_Geoc = newRegion.Geo AND opp. Owner_Area__c = newRegion.Area AND opp. Owner_Regionc = 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：
I can easy and always reproduce this issue from the project.
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.
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.