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?