How to incorporate OpenQuery into an SSDT project (without SQL71501/'unresolved reference to object' errors)

Question

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?

Answer 1

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 (ensure Suppress Reference Warning is checked).
• used the syntax OpenQuery([LINKEDSERVER], ‘xxx’); and not OpenQuery([$([LINKEDSERVER])], ‘xxx’);

I hope this helps!