SQL SQL71501 - 如何摆脱这个错误？

SQL71501 - How to get rid of this error?

提问by Jannik

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

我们在项目中使用了两个模式 ( 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.

当我们尝试使用以下 SQL 语句创建视图时，Visual Studio 在错误列表中显示为错误。

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].

SQL71501：计算列：[dbo].[RechenketteFuerAbkommenOderLieferantenView].[AbkommenId] 包含对对象的未解析引用。对象不存在或引用不明确，因为它可以引用以下任何对象：
[kal].[Basisrechenkette].[r]::[AbkommenId], [kal].[Rechenkette].[AbkommenId] , [kal].[Rechenkette].[r]::[AbkommenId], [kal].[Rechenkettenschema].[r]::[AbkommenId] 或 [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.

发布视图和工作就好了，但是在构建我们的项目时一直看到错误消息很烦人，所有严重的错误都在那些 sql 错误的混乱中丢失了。

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

你有什么想法，可能是什么问题？

回答by devinbost

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.

我刚刚找到了解决方案。尽管我无法阅读您的（似乎是德语）以了解您是否指的是系统视图，但如果是，则必须提供对 master 的数据库引用。否则，添加任何其他必需的数据库引用应该可以解决问题。

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

此处提供了其他信息：解决 SQL Server SSDT 项目中的歧义引用

回答by FlorianB

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.

在我的情况下，错误阻止我在 Visual Studio 2017 中执行 SqlSchemaCompare。然而，错误是针对不再属于解决方案的表的表/索引。简单的清理/重建没有帮助。

A reload of the visual studio solution did the trick.

重新加载 Visual Studio 解决方案即可解决问题。

回答by user6697603

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.

我们有一个项目，其中包含一个视图，该视图引用另一个数据库中的表值函数。添加解析远程数据库中使用的字段所需的数据库引用后，我们仍然收到此错误。我发现表值函数是通过使用“SELECT * FROM ...”定义的，这是由不熟悉良好编码实践的人创建的旧代码。我用需要的枚举字段替换了“*”部分并编译了该函数，然后为该数据库重新创建了 dacpac 以捕获结果模式，并将新的 dacpac 合并为数据库参考。呜呜！模棱两可的参考消失了！似乎 SSDT 引擎不能（或不）总是能够深入到引用的 dacpac 的内部以返回所有字段。

回答by Romain Reboulleau

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 TYPEstatement, 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.

我CREATE TYPE在 Visual Studio 2017 SQL Server 项目的脚本文件中遇到了相同的错误代码，因为我找不到如何专门从界面添加用户定义的类型。

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 Elementand use the search field to find the element you are trying to create.

解决方案是，在 Visual Studio 中，除了通过右键单击 >添加可以看到的那些之外，还有许多可编程性文件类型。只需选择“新建元素”并使用搜索字段查找您要创建的元素。

回答by Renzo Ciot

In my case, I got this error when I was trying to export the datatier application. The error was related to the link on a database user. To solve the problem, you need to log in to the server with read rights on system users.

就我而言，当我尝试导出数据层应用程序时出现此错误。该错误与数据库用户上的链接有关。要解决此问题，您需要以系统用户的读取权限登录服务器。

回答by JLWarlow

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.

对我来说，我在用户定义的表类型上看到了 SQL71501。原来我的解决方案中表类型的 sql 文件没有设置为 build.sql 文件。一旦我将构建操作从无更改为构建，错误就消失了。