I have a VS 2013 solution with two database projects.
One of them, let's call it the "worker" is supposed to get deployed to about 10 different production servers.
The other one, that is supposed to work as an "Controller" instance, is basically supposed to run different stored procedures on the "worker" database on the 10 production servers.
So to realize this, I have scripted 10 Linked Server objects within the "controller" and call the different SPs in the "worker" databases.
Here is a sample for the Linked Server scripts:
EXEC sp_addlinkedserver @server = N'PROD1', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'184.108.40.206\prod1' GO EXEC sp_addlinkedsrvlogin @rmtsrvname=N'PROD1',@useself=N'False',@locallogin=NULL,@rmtuser=N'aaaaa',@rmtpassword='aaaaa' GO EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc', @optvalue=N'true' GO EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc out', @optvalue=N'true' GO
So far so good, this works and the Linked Servers are created on the controller SQL Server and I can use them.
Within the SSDT project / solution, I get broken references whenever I use the Linked server objects. Sure, the schema is not aware of the linked server names, since they are only in a string constant in the above script.
Sample: (I translated the message on my own, no guarantee it's exactly right)
Warning 14 SQL71562: "Procedure: [dbo].[SP1]" contains an unresolved rerference to object [PROD1].[db1].[dbo].[SP1]".
So my problem is that I have kind of a reference from the Controller database schema to the "worker" database schema, but I can't use / insert a database-reference, because I use several different references (10 production servers) to "access" the worker's schema... I don't want to add 10 database references to my project with the "OtherServer" option (even if I could, I guess it won't allow to add 10 references to the same database project...)
I have a feeling that it should be possible to address the linked server objects in a correct manner in the solution, but I can't figure out how.
I tried Aaron's suggestion with Synonyms, but there I end in the same problem as soon as I use the linked server's name resulting in unknown objects "behind" the name.
I edited the question to make the problem more clear and hope anyone has an idea, because I want a clean solution without hundreds of WARNING messages.