Contains unresolved reference to an object issue - new bug?

[DavidWiseman]

Discussed in #234

^{Originally posted by DavidWiseman March 1, 2023}
Hi,

I have an SSDT project in Visual Studio 2022 that is built using msbuild. Within the last few days, the database project build has started to fail with an error similar to this:

Severity Code Description Project File Line Suppression State
Error SQL71501: Function: [dbo].[Test] 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: [sys].[partitions].[p]::[partition_number] or [sys].[partitions].[partition_number]. MSBuildIssue C:\Git\MSBuildIssue\MSBuildIssue\Test.sql 5
Severity Code Description Project File Line Suppression State
Error SQL71501: Computed Column: [dbo].[Test].[partition_number] 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: [sys].[partitions].[p]::[partition_number] or [sys].[partitions].[partition_number]. MSBuildIssue C:\Git\MSBuildIssue\MSBuildIssue\Test.sql 5
Severity Code Description Project File Line Suppression State
Error SQL71501: Function: [dbo].[Test] has an unresolved reference to object [sys].[partitions]. MSBuildIssue C:\Git\MSBuildIssue\MSBuildIssue\Test.sql 6

I thought it was an issue with my local machine, but the github action to build the database is now failing (without any changes made).

To reproduce the issue, create a new SSDT project in Visual Studio 2022 and add this function.

CREATE FUNCTION dbo.Test()
RETURNS TABLE
AS
RETURN
SELECT p.partition_number
FROM sys.partitions p

Add a database reference to master (which should fix this issue). Then try to build.

Should this be an issue here or somewhere else?

Thanks,

David

Update:

The difference seems to be between 17.4 and 17.5 of visual studio/msbuild.
This works:
MSBuild version 17.4.1+9a89d02ff for .NET Framework
This doesn't:
MSBuild version 17.5.0+6f08c67f3 for .NET Framework

I can get my github action to run in 17.4 by adding vs-version which is an option for a temporary fix.

     - name: Add msbuild to PATH
        uses: microsoft/setup-msbuild@v1.3
        with:
          vs-version: '[17.4,17.5)'

Hi,

I'm not sure if this is the right place to log this issue but there seem to be some bugs/breaking changes between mbsuild 17.4 and 17.5 for building a SSDT project.

Thanks,

David

[DavidWiseman]

I've decided to work around this issue for now:

• Moved the problematic functions from the build to the post-deployment step.
• Added suppressions to deal with the additional 71502 warnings where required.

[zijchen]

Can you check to see if this is still an issue? I can't reproduce it any more in VS 17.12.3. There were some issues with the system dacpacs in 2023 which could have caused this issue.