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”:

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!