3

I have a Visual Studio SSDT project where one view references a linked server via 4 part naming. I have set up a project for the database on the linked server and created a reference from the main project to the "linked server" project, and all works fine.

I have been instructed to use OpenQuery instead of 4 part naming convention because actually OpenQuery does run 2-3x faster than query via 4 part naming convention.

However, when I change the view in SSDT to use open query, I get an error: Severity Code Description Project File Line Suppression State Error SQL71501: View: [schema].[view] has an unresolved reference to object [LINKEDSERVER].

I have tried defining [LINKEDSERVER] in a script in the project:

exec master.dbo.sp_addlinkedserver @server = N'LINKEDSERVER', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'SERVERNAME'

And also tried adding same into the referenced project.

So, how to incorporate OpenQuery into an SSDT project without incurring SQL71501/'unresolved reference to object' errors?

OpenQuery erroring whereas 4 part works fine

ubienewbie
  • 1,771
  • 17
  • 31
  • You probably need to set up a database reference (likely with a dacpac for the external DB - or at least the objects you need in that external DB). Add that as a Database Reference under References for "different database, different server" and set the environment variables appropriately. That should let you reference it your linked server object(s). – Peter Schott Mar 01 '18 at 15:23
  • 1
    as mentioned in the question I have already created references (of type different database, different server) to the target database project and all works fine when I use 4 part naming. But it stops working when I use OpenQuery([LINKEDSERVER), '... – ubienewbie Mar 02 '18 at 16:53
  • Got it - you should be able to reference it using the $(LinkedServer).$(Database) variables. Although not ideal, there's always the option to use some sort of post-deploy script to create your proc/view as well. You shouldn't need to try to add a Linked Server to the project itself. The DB Reference will take care of that. – Peter Schott Mar 05 '18 at 22:54
  • Thanks Peter - I've added a screenshot which I hope illustrates the issue very clearly: 4 part naming convention (using the [$(LinkedServer)].[$(Database)].schema.table) works very nicely. But OpenQuery([$(LinkedServer)], 'select * from table') isn't working. Is it possible to use OpenQuery in an SSDT project without it generating errors? I wonder whether it isn't possible? – ubienewbie Mar 07 '18 at 09:40
  • That could definitely be the case. I will admit that I've not tried to include OpenQuery statements in my SSDT projects. I've used linked server queries once or twice, but always with 4-part names. – Peter Schott Apr 03 '18 at 22:21

1 Answers1

2

I’ve been fighting this same issue for a while. I solved by:

  • Importing the master.dacpac that included the needed Linked servers.
  • Creating a database reference from the calling database to the DACPAC of the database I'm referencing (insure Suppress Reference Warning is checked).

  • used the syntax OpenQuery([LINKEDSERVER], ‘xxx’); and not OpenQuery([$([LINKEDSERVER])], ‘xxx’);

    I hope this helps!

Poweredbytones
  • 81
  • 5
  • Great thanks for this I will try it (although have moved on from that situation where the issue was arising ...). By eye there does appear to be a typo, (open/close brackets not matching). For benefit of future users could you double check/correct? Thanks! – ubienewbie Oct 02 '19 at 11:17