1

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?

1
  • 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?
    – Vickey C
    Oct 9 '18 at 17:28
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'
    )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.