Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
I still can't get this to work despite having a master project with the LinkedServers.sql file there –  test Oct 16 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 at 9:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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