Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2014-09-10   |   Comments (6)   |   Related Tips: More > Application Development

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
Create a DACPAC File

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
Add Database Reference

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:

Publish the Database Project

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. 

Next Steps
  • Download the code here and try it out. 
  • I like the idea of using a TVF to encapsulate accessing a linked server; although I didn't show it here the TVF gives you the opportunity to specify parameters which could be useful.


Last Updated: 2014-09-10


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, December 19, 2017 - 8:18:16 AM - Pankaj Kumar Back To Top

Hi I am using linked server database as a project and still facing the SQL71561 error.  Here the following parameters.

Linked Database project name : databseDemo

Linked Server Name: DemoLinkedServer(Instance: PC100\DEMO)

Server Db name: DemoDataBase.

 

Queryin view(DemoView) referring to: [DemoLinkedServer].[DemoDataBase].[dbo].[Demotable]

Error : SQL 71561.dbo.DemoView has an unressolved reference to object [DemoLinkedServer].[DemoDataBase].[dbo].[Demotable]

 

I have also checked the option "supress errors caused by unressolved reference in the referenced project".

 

Thank you,

Pankaj Kumar

 

 


Tuesday, July 19, 2016 - 7:47:22 AM - Lukas Back To Top

 Luca Zavarella

 

adding Linked Server Item (Click on the project --> Add --> New Item --> Linked Server) did not helped to me, i cant still reference it but i did a workaround similar to artical.. i created a view with columns in remote table

CREATE VIEW vwDatabase1Table1

AS

SELECT 0 as Col1, 0 as Col2

In Predeployment script i altered the view to use a linked server and now i can reference it with columns.


Tuesday, September 15, 2015 - 11:10:34 AM - Gerald Britton Back To Top

I don't think this works for non-SQL Server linked servers (e.g. Oracle, MySQL, etc.).  At least I know of no way to extract a DACPAC file for them


Friday, July 31, 2015 - 12:04:54 PM - Ray Barley Back To Top

Take a look at this tip which shows how to access Excel via a linked server:

https://www.mssqltips.com/sqlservertip/2018/using-a-sql-server-linked-server-to-query-excel-files/

 


Friday, July 31, 2015 - 11:05:26 AM - Killion Mangwende Back To Top

How can i copy data using Link server from excel Table to sql database table.


Friday, May 29, 2015 - 9:54:29 AM - Luca Zavarella Back To Top

Hi Ray, in order to use a linked server without a database reference, you can simply add a Linked Server to your project:

Click on the project --> Add --> New Item --> Linked Server

 

In this file you add:

EXECUTE sp_addlinkedserver LINKEDSERVERNAME
GO

Regards.


Learn more about SQL Server tools