How to Use a Linked Server in a SQL Server Database Project

Problem

I use the SQL Server Database Project for developing, maintaining, and publishing/deploying my databases.  I added some views to my database project that query data via a linked server and now I am not able to successfully build my project.  The first error I get is SQL71561 - the view has an unresolved reference to the object referenced by the linked server.  I did some research and found out that you can use a linked server if you add a DACPAC file to the project.  However, when I tried to create the DACPAC I get error SQL71564 - the element cannot be deployed as the script body is encrypted.  Can you help me to get this working?

Solution

In order to query using a linked server in a database project, you have to add a database reference to your project which requires that you create a DACPAC file.  Unfortunately as you have noted, you cannot create a DACPAC file on a database that has objects that have been created "WITH ENCRYPTION". I have run in to this problem myself and I will offer my solution in this tip. 
I will cover the following tasks in this tip:

• Create a view that can access a linked server
• Create a DACPAC file for a database referenced by a linked server
• Add a database reference to the database project for the linked server
• Publish the database project
• Demo the sample view that uses a linked server
• Add a view that uses the linked server without adding a database reference to the project

I am going to assume that you are familiar with the SQL Server database project.  If you haven't used it before or need a refresher, take a look at the Creating a New Database Project Tutorial.  Although it's based on the 2010 version, it will get you up-to-speed quickly.
I am using the SQL Server Data Tools (SSDT) for Visual Studio 2012 for this tip.  You can download SSDT  here. 

Create a SQL Server View

Add the following view to the database project:

CREATE VIEW dbo.Product
AS
SELECT [ProductID], [Name]
FROM [BARLEYWIN8\SQL2012].[AdventureWorks2012].[Production].[Product]

When you build the project, you will see the following error:
SQL71561: View: [dbo].[Customer] has an unresolved reference to object [BARLEYWIN8\SQL2012].[AdventureWorks2012].[Production].[Product].
[BARLEYWIN8\SQL2012] is a linked server defined on my machine.  However, the database project cannot resolve this reference yet.

Create a DACPAC File

A DACPAC file (also known as a Data-tier application) is defined here as "a logical database management entity that defines all of the SQL Server objects - like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC".
Follow these steps to create a DACPAC file for a database (e.g. AdventureWorks2012):

1. Open SQL Server Management Studio (SSMS)
2. Connect to the SQL Server instance for your database
3. Right click the database in the Object Explorer
4. Select Tasks, Extract Data-tier Application from the menu
5. Fill in the dialog as shown below

Note the folder location and file name of the DACPAC file; you will need this to add a database reference to your database project. 

Add Database Reference

In general when your database project references objects in another database, you must add a database reference to your project. 
Follow these steps to add a database reference for a linked server:

1. Right click on References in the Solution Explorer
2. Select Add Database Reference from the menu
3. Fill in the dialog as shown below

The following are the main points about the above dialog:

• Click the Data-tier Application (.dacpac) radio button
• Fill in the full path to the DACPAC file (we created this in the previous step)
• Select Different database, different server for Database location; this is typically the case for a linked server
• Specify the name of the database you will access via the linked server
• Specify the server name; this is the server name of the linked server; include \instance if a named instance
• Database variable is the name of the SQLCMD variable that will be added to the project; use this to set different values for the database name based on the publishing profile
• Server variable is the name of the SQLCMD variable added to the project; use this to set different values for the server name based on the publishing profile

Now that we have added the database reference to the project, update the view that we created earlier to use the SQLCMD variables in the database reference. Build the project and the SQL71561 error is resolved.

CREATE VIEW [dbo].[Product]
AS 
SELECT [ProductID], [Name]
FROM [$(AW_LINKED_SERVER)].[$(AW_DATABASE)].[Production].[Product]

Publish the Database Project

The database project has a Publish function that will create or update a target database based on the project contents.  Right click the project in the Solution Explorer and select Publish from the menu.  Fill in the dialog as shown below:

Main points about the above dialog:

• The target database connection is a SQL Server 2014 instance on my laptop
• The AW_LINKED_SERVER SQLCMD variable value of BARLEYWIN8\SQL2012 is a linked server on my laptop
• Click the Save Profile As button to save the publishing profile in the project; you can double click it to publish again with the saved values
• Click Publish to create or update the database on the target

Run the query SELECT * FROM dbo.Product to verify that the linked server works.

Use a Linked Server Without a Database Reference

Now that we have seen how to use a linked server with a database reference, let's see how to use a linked server without a database reference.  This is a work around for the situation where you are unable to create a DACPAC file; e.g. some database objects were created "WITH ENCRYPTION".
The solution that I am using in this case is as follows:

• Create a dummy table-valued function (TVF) that defines the result set from the query that uses a linked server; e.g. create the TVF, but do not return any rows
• ALTER the TVF in the post-deployment script; put in the select statement that references the linked server
• Create a new view to select from the TVF

Here is the dummy TVF:

CREATE FUNCTION [dbo].[Product_TVF]
(
)
RETURNS @returntable TABLE
(
 ProductID int,
 Name nvarchar(50)
)
AS
BEGIN
 RETURN
END

Note that there is no select statement in the TVF; it is merely a placeholder for the linked server reference that allows the database project to build successfully.  Create the new view as follows:

CREATE VIEW [dbo].[Product2]
AS 
SELECT * 
FROM dbo.Product_TVF()

Add the following to the post-deployment script:

ALTER FUNCTION [dbo].[Product_TVF]
(
)
RETURNS @returntable TABLE
(
 ProductID int,
 Name nvarchar(50)
)
AS
BEGIN
  INSERT @returntable
  SELECT ProductID, Name
  FROM [BARLEYWIN8\SQL2012].[AdventureWorks2012].[Production].[Product]
  RETURN
END

Note that I used the original four-part name in the FROM clause; i.e. linked server name and database name without the SQLCMD variables to show that the database project does not perform any validation on the T-SQL code in the post-deployment script. 

source collected from mssqltips.com