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.

Settings for Database Reference

Initial Script Version

Here is a shorter version of the original view creation:

CREATE VIEW dbo.vwStatus
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].

First Attempt

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).

Further Attempts

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)

1 Answer 1


I've managed to get it working:

I created a new database project master. In there I created a folder Server Object and a file LinkedServer.sql. In the SQL file i added the linked server:

EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';

After adding the database Project master to my solution and referencing it in my original databse project, I was able to build the project using the initial syntax;

CREATE VIEW dbo.vwStatus
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1
  • I still can't get this to work despite having a master project with the LinkedServers.sql file there
    – test
    Oct 16, 2014 at 22:14
  • Well, it's hard to help without some more information. perhaps it's better to make another question. It's important to reference your master project in your solution. otherwise this worked for me exactly as described.
    – Chake
    Oct 17, 2014 at 9:00
  • Within your solution - do you have a dacpac(s) or project(s) with the LinkedServer DBAs? Jun 10, 2015 at 17:12
  • Hi, I can't remember, but I wrote in the second paragraph, that I've used dacpacs. But for the final solution I referenced a database project for the master DB.
    – Chake
    Jun 18, 2015 at 10:31
  • 1
    Note you must make sure your 'LinkedServer.sql' file has build action set to 'Build'. Annoyingly VS doesn't seem to recognise the linked server from a Pre Deployment script, or all this wouldn't be necessary.
    – Taran
    Nov 15, 2016 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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