Contains unresolved reference to an object issue - new bug?

[DavidWiseman]

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/[email protected]
        with:
          vs-version: '[17.4,17.5)'

[chadbaldwin]

Deleted my original answer because I didn't see your last few comments in your post. And I didn't think about the fact that this particular sys table is specific to the user database, not the master database, so now I realize why it's an odd "error".

Also, hi David 👋, I didn't realize it was you when I first answered.

[DavidWiseman]

👋Hi, thanks for responding. Having the reference to the master DB solved this problem in the past but it looks like it just got broken.

This GitHub action built the DBA Dash database 3 days ago successfully:
https://github.com/trimble-oss/dba-dash/actions/runs/4276862247/jobs/7445258511

I ran the same action on my clone of the repo (which doesn't have any code changes from 3 days ago) it fails:
https://github.com/DavidWiseman/dba-dash/actions/runs/4307819664/jobs/7513343158

Interestingly if you put the SELECT statement in a stored procedure rather than a function it will build but with SQL71502 warnings instead of SQL71501 errors.

Maybe this is related to #83

[chadbaldwin]

I forgot to reply back to you on this. But I'm pretty sure this has always been in place, but maybe you're just now running into it?

SSDT tries its best to replicate what SQL Server would do in a deployment. So it tries to mimic defferred name resolution rules.

If you put a non-existent table reference into a proc, nothing will happen, because of deferred name resolution.

e.g., create a proc that just contains

CREATE PROC dbo.usp_Test
AS
SELECT *
FROM dbo.NonExistentTable

This will deploy just fine because SQL Server doesn't resolve the table name until runtime. So in SSDT, it will show a warning.

If you try the same thing with a function or a view, those will break because deferred name resolution does not apply to funcs/views. So SSDT will show an error.

However, deferred name resolution does not apply to column names on existing tables. So if you try this:

CREATE PROC dbo.usp_Test
AS
SELECT NonExistentColumn
FROM dbo.ExistingTable

This will not run and will throw an exception. So in this case, SSDT would show an error.

So with that in mind...if you are referencing a system table and SSDT can't find it because there's no project database reference to master, it will just show a warning.

But if you reference a system table in a func/view and you don't have a project database reference to master, then it will show an error and break the build.

[chadbaldwin]

Just to be clear, I was just responding to your point about why it returns an error vs warning. See you were able to narrow it down to it being a new issue between updates of MSBuild.

[DavidWiseman]

That makes sense. Deferred name resolution doesn't apply to functions so it generates an error instead of a warning.

I expect more people will be impacted by this msbuild bug in 17.5 but it might go unnoticed and has less impact if its just generating warnings instead of errors. I've just moved the functions to the post deployment script to work around the issue for now.

Thanks

[Naren9918]

In my case i'm using Tempdb in the function and i'm getting unresolved reference error for Tempdb
Not able to add database reference of temdb because it is not showing in system database.

How t will be fix?

Thanks,
Narendra Pate

[DavidWiseman]

I've logged issue #236 for this after narrowing down the issue to differences between visual studio/msbuild 17.4 and 17.5.

[DavidWiseman]

I'm working around this issue:

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