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?

  • Have you added the master .dacpac, or a reference to the master database? One (or possibly both, depending on who you believe) is needed to use an OPENQUERY on a linked server. – Laughing Vergil Oct 9 '18 at 17:19
  • Yes, Laughing Virgil, I have both master and the .dacpac for the referenced database listed in the database project references. – Vickey C Oct 9 '18 at 17:28
  • But do you have the .dacpac for master referenced? – Laughing Vergil Oct 9 '18 at 17:46
  • 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. Are you suggesting that I need to add a reference for the master database of the server being referenced? – Vickey C Oct 9 '18 at 18:15

Your Answer

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

Browse other questions tagged or ask your own question.