I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only one error left:
In one view the devs used
OPENQUERY to access a linked server. So I imported a DACPAC which contains the right database and added it to the project by using
Add Database Reference using the following reference options.
Initial Script Version
Here is a shorter version of the original view creation:
CREATE VIEW dbo.vwStatus AS SELECT StatusID, StatusName FROM OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1
This lead to the following error:
Error 136 SQL71501: View: [dbo].[vwStatus] has an unresolved reference to object [LinkedServer].
So I tried to insert the server name variable
FROM OPENQUERY($(LinkedServer), 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1
Which leads to
Error 176 SQL46010: Incorrect syntax near $(LinkedServer).
I fiddled arround a bit and tried the following (with and without having quoted identifiers enabled):
FROM OPENQUERY("$(LinkedServer)", 'SELECT * FROM [DB].[dbo].tbStatus') AS FROM OPENQUERY([$(LinkedServer)], 'SELECT * FROM [DB].[dbo].tbStatus') AS FROM OPENQUERY([LinkedServer], 'SELECT * FROM [DB].[dbo].tbStatus') AS FROM OPENQUERY("LinkedServer", 'SELECT * FROM [DB].[dbo].tbStatus') AS
I am always getting an error.
I have no clue what I'm overlooking here. Do you? Thanks for your time!
(Sadly I can't add the visual-studio-2013 tag, so I used visual-studio)