3

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.

  • 1
    (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.) – Aaron Bertrand Aug 9 '17 at 18:54
3

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;

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.