Correctly handling Linked Server objects in SSDT


Recently I had spoken at the SQL Bangalore User Group about using SSDT and Visual Studio Online to have an efficient and agile development process for the cloud-first world. After my talk meeting, an attendee had asked me about correctly handling four-part names (objects referenced in other servers).

Problem

In his case he had added the referenced DB as a reference, but was still getting the following warning (error if you set the ‘treat warnings as errors’ property):

SQL71561: View: [dbo].[myView] has an unresolved reference to object [remserver].[someDB].[dbo].[RefTable].

Here, myView is the referencing object and someDB is the referenced project (which was already added by him as a database reference.

Solution

Here is how to meet this requirement. Firstly, you must have SSDT projects for both the databases in question. Add them to the same solution, and then setup a reference from your referencing project to the project which contains the object. The important thing is when you do that you can set the “Database Location” option. Make sure you set that as “Different database, different server”:

image

Once you add this reference using the above option, it is very easy to reference the linked server object in your main project. You do that by qualifying the referenced object with the prefix of the above variables defined in the Database Reference. For example, in the above case it is [$(OtherServer)].[$(RefDB)].<schema>.<object>:

CREATE PROCEDURE [dbo].[Mismatch]
    @param1 int = 0
AS
    SELECT COUNT(*) FROM dbo.Orders
    where CustomerID = @param1

    SELECT Id FROM [$(OtherServer)].[$(RefDB)].dbo.RefTable
RETURN 0

And here is a sample view:

CREATE VIEW myView
    As
    SELECT Id FROM [$(OtherServer)].[$(RefDB)].dbo.RefTable

Hope this helps!

Comments (4)

  1. Chintak says:

    Arvind, we also use the same method described by you. The problem with this approach is I tend to get these views as diff output when I compare the DACPACK files. Just checking if you have any solution to this diff problem.

  2. Good point, Chintak. Can you clarify exactly what diff you are referring to? Is it the drift report or a schema compare? If schema compare, which 2 DACPACs do you compare?

  3. dev says:

    Hi Arvind.
    How can I get database referencing in distributed partitioned view scenario where all DB instances must have references to each other?

  4. Grant Smith says:

    Greetings…. I did this and checked the box for “Suppress errors caused by unresolved references in the referenced project” but my project still won’t build, still with SQL71561 errors…..

Skip to main content