SQL71501 - How to get rid of this error?

Question

We're using two schemas in our project (dbo + kal).

When we are trying to create a view with the following SQL statement, Visual Studio shows as an error in the error list.

CREATE VIEW [dbo].[RechenketteFuerAbkommenOderLieferantenView]
AS
    SELECT
        r.Id as RechenkettenId,
        r.AbkommenId,
        r.LieferantId,
        rTerm.GueltigVon,
        rTerm.GueltigBis,
        rs.Bezeichnung,
        rs.As400Name
    FROM
        [kal].[Rechenkette] r
    JOIN
        [kal].[RechenketteTerm] rTerm ON rTerm.RechenketteId = r.Id
    JOIN
        [kal].[Basisrechenkette] br ON rTerm.BasisrechenketteId = br.Id
    JOIN
        [kal].[Rechenkettenschema] rs ON rs.Id = br.Id
    WHERE 
        r.RechenkettenTyp = 0

The error message looks like this:

SQL71501: Computed Column: [dbo].[RechenketteFuerAbkommenOderLieferantenView].[AbkommenId] 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:
[kal].[Basisrechenkette].[r]::[AbkommenId], [kal].[Rechenkette].[AbkommenId], [kal].[Rechenkette].[r]::[AbkommenId], [kal].[Rechenkettenschema].[r]::[AbkommenId] or [kal].[RechenketteTerm].[r]::[AbkommenId].

Publishing the view and working is just fine, but its quite annoying to see the error message all the time when building our project having all the serious errors get lost in the shuffle of those sql errors.

Do you have any idea, what the problem might be?

Answer 1

I just found the solution. Although I can't read your (what appears to be German) enough to know if you're referring to system views, if so, a database reference to master must be provided. Otherwise, adding any other required database references should solve the problem.

This is described here for system views: Resolve reference to object information schema tables

and for other database references.

Additional information is provided here: Resolving ambiguous references in SSDT project for SQL Server

Answer 2

For me I was seeing SQL71501 on a user defined table type. It turned out that the table type's sql file in my solution wasn't set as build. As soon as I changed the build action from None to Build, the error dissapeared.

Answer 3

I know this is an old question but it was the first one that popped up when searching for the error.

In my case the errors were preventing me from executing the SqlSchemaCompare in Visual Studio 2017. The error however was for a table/index of a table that was not part of the solution any more. A simple clean/rebuild did not help.

A reload of the visual studio solution did the trick.

Answer 4

We have a project that contains a view that references a table valued function in another database. After adding the database reference that is required to resolve the fields used from the remote database, we were still getting this error. I found that the table valued function was defined by using "SELECT * FROM ..." which was old code created by someone not familiar with good coding practices. I replaced the "*" portion with the enumerated fields needed and compiled that function, then re-created the dacpac for that database to capture the resulting schema, and incorporated the new dacpac as the database reference. Woo Hoo! the ambiguous references went away! Seems that SSDT engine cannot (or does not) always have the ability to reach down into the bowels of the referenced dacpac to come back with all the fields. For sure, the projects I work on are normally quite large, so I think it makes sense to give the tools all the help you can when asking them to validate your code.

Answer 5

Although this is an old topic, it is highly ranked on search engines, so I will share the solution that worked for me.

I faced the same error code with a CREATE TYPE statement, which was in a script file in my Visual Studio 2017 SQL Server project, because I couldn't find how to add a user-defined type specifically from the interface.

The solution is that, in Visual Studio, there are many programmability file types, other than the ones you can see through a right-click > Add. Just select New Element and use the search field to find the element you are trying to create.

Answer 6

From the last paragraph of the blog post Resolving ambiguous references in SSDT project for SQL Server, which was linked in the answer https://stackoverflow.com/a/33225020/15405769 :

In my case, when I double clicked the file and opened it I found that one of the references to ColumnX was not using the two part name and thus SSDT was unable to determine which table it belonged to and furthermore whether the column existed in the table. Once I added the two part name. Bingo! I was down to no errors!