Sql-server – SSDT project with working linked server references gets SQL71562 warning

database-projectslinked-serversql-server-2008-r2ssdtvisual studio

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'1.2.3.4\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.

BUT:
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.

Best Answer

According to the message shown in the Question, this is actually a warning ("warnung") and not an error. An error will stop the build process and prevent the project from being published to the server. Warnings merely show up in the "Error List" window, but don't prevent the build or publish processes from completing.

I created a Database project in Visual Studio 2013 and added a stored procedure that selected from a Linked Server. If the Linked Server name did not match a valid Linked Server on SQL Server, then there was a publication error as reported by SQL Server (not Visual Studio). Otherwise, everything worked as expected. But in both cases, my Linked Server reference in that Stored Procedure in Visual Studio was underlined in blue AND the following message appeared in the "Error List" window after the build finished:

Warning 2   SQL71562: Procedure: [dbo].[GetExpressDBs] has an unresolved reference to
            object [LStoSqlExpress2014].[master].[sys].[databases].

If you don't want to see these warnings, there are two things you can do:

  1. You can simply not show them in the "Error List" window by clicking the button at the top of the "Error List" window that says "X of Y Warning(s)". If you do this, you won't see ANY warnings here (even non-Linked Server-related warnings), but your code will still show the blue underlines that will display the warning if you hover over them.

  2. You can tell Visual Studio / SSDT to ignore just these specific warnings (but still show the others) by doing the following:

    1. Go to Project Properties
    2. Go to the "Build" tab
    3. Enter the following into the "Suppress Transact-SQL warnings:" text field:
      71562

    Doing the steps noted above will prevent just this one specific warning from showing up in the "Error List" window as well as the blue underlines in the code using the Linked Servers.

Related Question