View with OPENQUERY won't build in SSDT project

Question

I have an existing database that I'm trying to set up as a database project. I'm not able to get the following statement to build:

CREATE view [dbo].[VW_SOMEVIEW] as 
SELECT * FROM OPENQUERY ([SERVER2],'
SELECT SESSION_NAME,
 SESSION_ROWS,
 ACTUAL_START
FROM [DATABASE2].[dbo].[TABLE2] (NOLOCK)
WHERE SUBJECT_AREA = ''XYZ''
order by ACTUAL_START '
)

I'm getting the error:

SQL71501: View [dbo].[VW_SOMEVIEW] has an unresolved reference to object [SERVER2].

When I use the variable name for the server instead of the actual server name, I get:

SQL46010: Incorrect syntax near $(LinkedServer).

Both the server containing the view and the server being referenced are on SQL Server 2016, and I'm using Visual Studio 2017.

I've added the .dacpac file from SERVER2 in References, and I've tried using variables for the server and database name, but nothing works. What am I missing here?

• Vickey C: I have both master and the .dacpac for the referenced database listed in the database project references. I am referencing the master .dacpac that was supplied in Visual Studio. I added this to the project by right-clicking on References -> Add Database References -> System Database -> master. Do I need to add a reference for the master database of the server being referenced?

Answer 1

Have you tried this?

CREATE VIEW dbo.VW_SOMEVIEW AS
SELECT *
FROM OPENQUERY (
 [$(LinkedServer)] /*we need the [] brachets to make this parse ok in VS*/
 ,'SELECT SESSION_NAME,SESSION_ROWS,ACTUAL_START
 FROM DATABASE2.dbo.TABLE2 (NOLOCK)
 WHERE SUBJECT_AREA = ''XYZ''
 ORDER BY ACTUAL_START'
 )