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

linked-serversql serversql-server-2016

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.

Best Answer

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;
Related Question