4

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.

  • 1
    I can't create a SYNONYM within the project for the linked server "PROD1" because then the CREATE SYNONYM statement itself will complain about using the unknown references "behind" "PROD1" to create the synonym. – Magier Sep 7 '15 at 16:39
2

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.

  • 1
    TY for caring and this detailed information. I am aware of these options, sorry not to mention. Suppressing always is the very last option to me. What I was looking for is a solution, the best practice way to correctly and regulary implement this functionality. After about 2 years suspending work with ssdt projects, I was very much hoping there finally is a celan solution for this scenario out there. – Magier Sep 8 '15 at 16:32
  • 1
    @Magier I understand that suppressing the warnings is not ideal as it might suppress other warnings that you do need, but I don't think there will be a way around this. From everything that I can see, SSDT is very database-focused, and this is a server-focused item. I mean, in order to validate across a linked server, it would need to do more than the typical meta-data lookup; it would need to do those meta-data queries over that linked server. But maybe somebody else has an idea? – Solomon Rutzky Sep 9 '15 at 2:04
  • Since I have the target db in my solution it's not that far away. Server Objekts existed years ago in the former Vsdb Server Projects... Linked Server exists as item template in ssdt... Looks like it's not implemented correctly by Ms...? Lets see if anyone has ideas. – Magier Sep 9 '15 at 5:53
  • You can also suppress the warnings on a per warning and per file basis by doing in the a specific file's properties pane. – gonsalu Aug 30 '16 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.