Remote query (openquery) to linked server fails after upgrading to SQL Server 2016

Question

Problem started after upgrading to SQL Server 2016 Enterprise edition. Linked and local servers both were upgraded to ver.13.0.4435.0

I've tried the following: Drop/create the linked server after the upgrade to 2016. The problem persists.

This is the code snippet where error occurs:

SELECT t.NAME
 , t.Disabled
FROM openquery([Linked_Server], 'select t.Name, t.Disabled 
 from [Linked_Server].[Database-1A].[dbo].[My_tbl] t where t.ID=232627') t;

And the error message:

OLE DB provider "SQLNCLI11" for linked server "Linked_Server" returned message "Deferred prepare could not be completed.". Msg 5000 Could not find server 'linked server' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

• Aaron Bertrand: (FWIW, it's unlikely this exact query worked before the upgrade, again, unless there was a loopback linked server that was changed or removed as a separate part of the upgrade.)

Answer 1

The query you're sending to the linked server (via OPENQUERY) shouldn't also contain its own reference to the linked server (via 4-part name), unless the linked server has its own loopback linked server with the same linked server name (and even if it does, I wouldn't try to stack linked servers like that). Try:

SELECT t.Name, t.Disabled
FROM OPENQUERY
(
 [Linked_Server], 
 N'select t.Name, t.Disabled 
 from [Database-1A].[dbo].[My_tbl] AS t 
 where t.ID=232627;'
) AS t;