Sql-server – How does “OtherServer” in SSDT database references work

sql serversql-server-2008-r2ssdt

I want to create a SSDT database project that is supposed to be deployed on a Sql Server that has several linked servers to other servers. The database ("MyBusinessDB") on those remote servers is in another SSDT solution, so I have this as "MyBusinessDB.dacpac".

Since we are allowed to add references to other databases or DACPAC files to correctly reference database objects, I would like to add the MyBusinessDB.dacpac as a reference to my project.

This article by Peter Schott explains the basics about adding database references.

There it's also possible to define that the referenced database resides on "Other/Different Server".

I referenced the MyBusinessDB.dacpac file and configured it to be on "same server". It's possible to use it's schema objects and reference them in Sql code.

But since I want to use this database on remote servers, I need to add a linked server name in advance. The Linked Server name should be variable because there are several instances. Unfortunately as soon as I configure the database reference to be on another/different server, visual studio fails to validate the schema objects of the referenced MyBusinessDB.dacpac file.

I tried different syntax, also the suggested syntax by the reference creation dialog itself (sample/suggestion is visible on bottom of this window):

SELECT * FROM [$(OtherServer)].[$(MyBusinessDB)].[Schema1].[Table1]

My naive expectation is that it's understood as: "I know what you mean, you are referencing the MyBusinessDB.dacpac by the variable "MyBusinessDB" and telling me that you want to use this on "OtherServer" you are going to fill with a value lateron…"

But using this I also get compiler errors. The dacpac's schema is never resolved.

Since I did just reference the exact same dacpac file and just added another variable (servername) in front of it, why is this failing to correctly read the dacpac's schema? How should this "feature" be used correctly?

Best Answer

I just tried this locally and it resolves fine and also complains about the one column that doesn't exist.

enter image description here

Check your reference properties I have highlighted the ones used in the four part name.

enter image description here