Database link in SSDT project with UDF and views

Question

Running into a weird problem. Assuming that in one empty solution there are two database projects, Bart and Homer. Bart was added as a reference to the database for Homer.

The 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's project then defines a table:

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

and view:

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

When I try to create, I get the following 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]. 

How should I correctly indicate Bart UDFs in a view?

Answer 1

When you add a link to a database, by default it sets the database variable for use in your TSQL scripts. This allows your project to focus on several environments where the link to the database may have a different name.

Proper use given this default value:

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

I checked that this works for the piece of code you submitted. Please note: if you want to use the database name directly, you can simply delete the value of the database variable when adding the link. Then Bart.dbo.Message () will work as expected for you.

Below is the Add Database Link dialog with the corresponding database variable and usage example. You will see a change in usage depending on whether there is text in the text field of the database variable.