SQL Server 2008 – Using Linked Server with OPENQUERY in Database Project

linked-serversql-server-2008ssdtviewvisual studio

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)

Best Answer

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
Related Question