Data-Tier Projects and Linked Servers

Question

I'm trying to use a Data-Tier application involving a linked server and running into an odd problem.

Here's what I have:

1) A VS2010 solution which includes a Data-Tier project.

2) The Data-Tier project targets an SQL 2008 R2 server.

3) The SQL server has a working linked server connection to an Oracle database.

4) Views within the Data-Tier project reference the linked server using OPENQUERY

The linked server connection is valid, running queries against it on the server work fine, and creating the views directly on the server works fine as well.

However, whenever I attempt to build my Data-Tier application I get the following error:

SQL03006: View: [dbo].[vwMyExampleView] has an unresolved reference to object [MyExampleLinkedServer].

Answer 1

Essentially the problem is that the SQL Server Database project doesn't have a way to resolve the Link to the Oracle database's tables.

I had a similar problem when trying to reference a table in a different database on the same server. In my case I was using SQL Server for both databases, and created a database project for the other database, added it to the solution for the first project, then created a reference to the second project from the first. I also used SQLCMD variables in synonyms so that I would be able to change the synonym, and all my proc and view references would not need to be updated.

I'm not sure this will work between SQL Server and Oracle since the Visual Studio database edition only supports SQL Server. (AFAIK) You may be able to find a solution by creating a .dbschema file and adding the Database reference to your SQL Server project.

More details can be found at this link:

http://msdn.microsoft.com/en-us/library/bb386242.aspx