带有 UDF 和视图的 SSDT 项目中的数据库参考 [英] Database Reference in SSDT project with UDFs and Views

问题描述

遇到一个奇怪的问题.假设在一个空解决方案中有两个数据库项目，Bart 和 Homer.Bart 已被添加为 Homer 的数据库参考.

Running into a weird issue. Assuming there are two database projects in one empty solution, Bart and Homer. Bart has been added as a Database Reference to Homer.

Bart 项目定义了一个函数:

Bart project defines a function:

CREATE FUNCTION [dbo].[Message]()
RETURNS NVARCHAR(255)
AS
BEGIN
    RETURN 'I am a value returned from another database'
END

然后 Homer 项目定义了一个表:

Then the Homer project defines a table:

CREATE TABLE [dbo].[Messages]
(
     [Id] INT NOT NULL PRIMARY KEY
)

和视图:

CREATE VIEW [dbo].[MessagesV]
    AS SELECT Id, Bart.dbo.Message() AS [Message]
    FROM dbo.Messages

尝试构建时，我收到以下错误:

When trying to build, I am getting these errors:

Error   2   SQL71501: Computed Column: [dbo].[MessagesV].[Message] 
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: [Bart].[dbo].[Message] or 
[dbo].[Messages].[Bart]::[dbo].[Message].   

Error   1   SQL71501: View: [dbo].[MessagesV] 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: 
[Bart].[dbo].[Message] or [dbo].[Messages].[Bart]::[dbo].[Message]. 

我应该如何在视图中正确引用 Bart UDF?

How should I reference Bart UDFs correctly in a view?

推荐答案

当您添加数据库引用时，默认情况下它会设置一个数据库变量以在您的 TSQL 脚本中使用.这允许您的项目面向多个环境，其中引用的数据库可能具有不同的名称.

When you add a database reference, by default it sets a database variable to use in your TSQL scripts. This allows your project to target multiple environments, where the referenced DB may have a different name.

给定此默认值的正确用法是:

The correct usage given this default would be:

CREATE VIEW [dbo].[MessagesV]
AS SELECT Id, [$(Bart)].dbo.Message() AS [Message]
FROM dbo.Messages

我已验证这适用于您发送的代码段.请注意，如果您确实想直接使用数据库名称，则可以在添加引用时简单地删除数据库变量值.然后 Bart.dbo.Message() 将按预期为您工作.

I've verified this works for the code snippet you sent. Note that if you do want to use the Database Name directly you can simply delete the Database Variable value when adding the reference. Then Bart.dbo.Message() will work as expected for you.

下面显示的是添加数据库引用"对话框，其中显示了相关的数据库变量和示例用法.根据数据库变量文本框中是否有任何文本，您将看到用法变化.

Shown below is the Add Database Reference dialog with the relevant Database Variable and Example Usage shown. You will see the usage change depending on whether there is any text in the database variable text box.

这篇关于带有 UDF 和视图的 SSDT 项目中的数据库参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！