Sql-server – View with OPENQUERY won’t build in SSDT project

linked-serversql serversql-server-2016ssdtvisual studio

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?

Best Answer

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'
    )
Related Question