Using a linked server with OPENQUERY in a database project

Question

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

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)

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:

GO
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
AS
SELECT StatusID, StatusName
FROM OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

• test: I still can't get this to work despite having a master project with the LinkedServers.sql file there
• Chake: 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.
• Martin Meeser: Within your solution - do you have a dacpac(s) or project(s) with the LinkedServer DBAs?
• Chake: 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.
• Tar: 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.