Do I need a database reference for a linked server in a SQL Server database project?

Question

In my database project, I have added a reference to a linked server. When I use this linked server in a view and try to build my database project, SSDT reports errors because it cannot understand references to any of the schemas referenced on the linked server:

[LinkedServer].[DB1].[dbo].[Table1]

The above would returns an error that SSDT cannot decipher the reference to [DB1].[dbo].[Table1]. I tried to add a reference to this database, but SSDT required either a .dacpac file (produced by another database project) or a system database on the same server as the database in my project.

How do I handle referencing an external database? There are use cases where a project needs to reference an remote database that is not an SSDT database project. In my case, I am accessing the database of another company and putting this database under version control as a SSDT project is out of the question.

Positron

As a workaround you can create a view in your local server that select * from a remote server – Prater

@Prater Could you explain how this is a workaround? – Positron

because SSDT will reference to the view on the local server, instead of the table on the remote server – Prater

@Prater Then I would have to recreate that view for each deployment destination, and I would also need a view whenver I wanted to reference the linked server in a new query. – Positron

@Positron - when I ran into this issue (it was ages ago) I was able to create a project for my linked server db, pull all the schema objects in, have it build the required file, copy it into my actual db project, and then reference it. It also required having a 'server' project as well, as that was where the linked server setup was done (this may have changed, I don't know) - but I was able to have it deploy the solution as a whole in the end. – Induct

Answer 1

Create a new SQL project for the remote database, place any objects in the project that you need to reference (doesn't have to be the whole database), and then add that project as a Database Reference to your project. You don't have to deploy the remote database, just have the definition of objects you use so they can be referenced.

Cosset

Can you please be more specific ? If I create new sql project in the same solution and reference that project, create new table object in the new project, how can I specify database name and how to reference it from the old project ? It means that the project name is the linked server name ? – Templin

@Templin When you add the database reference the dialog has fields for specifying the database-name and server-name and also variables for referencing them in your SQL scripts within the project. The most useful part of this dialog is the sample SELECT statement at the bottom where it shows you how to use the variables. – Larrikin

Answer 2

The option we finally settled on was to use SSIS for importing of data. This way, transfer of remote data happened in an ETL layer. Our database did not reference any remote databases this way, which also can improve performance (eliminates transfers over the network, cross server joins etc).

I would recommend using SSIS or a similar method to ETL your data into local tables that your database project can reference (without needing an external project reference).

Positron