Post reply

linked server cached somewhere?

  • June 21, 2017 at 7:54 am

    #331317

    Hi there
    I am trying to resolve a problem for a customer. They have a job which calls a stored proc on Server A, which calls a stored proc on Server B, via a linked server called RefreshDB. The linked server references a DNS alias, pointing to Server B. Recently we migrated all the databases on ServerB to ServerC. All is working fine, but when we updated the DNS alias from referencing Server B to Server C, the job on Server A started failing. Basically, the stored procedure fails saying it cannot locate RefreshDB in sys.servers. RefreshDB is the linked server, which does exist in sys.servers. It references the DNS alias. If i ping that alias from Server A, I get Server C, which is correct. If I change the linked server to reference the actual server name, Server C, instead of the alias, it works. If i change the linked server to point to our test server, Server Test, it works. The problem is with the reference to the live server, Server C.... I have tried deleting RefreshDatabases linked server, and creating a test one called TestLink. TestLink is configured to point to Server C. When i try and run the job it fails saying cannot find RefreshDatabases! But why? I have dropped it! I even used sp_dropservers 'RefreshDatabases'. If i update TestLink to point to another server, it works. It's just when we refrence ServerA that we get a failure referencing RefreshDatabases. There is some sort of cached setting that is causing problems here, but I just don't know where! Any thoughts???

    Thanks

  • June 21, 2017 at 8:51 am

    #1947575

    I am a little confused.  You started with a linked server named RefreshDB, but it morphed into RefreshDatabases, or was that another test? 

    Also, ServerA was the server with the Linked Server Definition, but at the end, it is also the destination?

    From the initial description, I would have blamed the local client DNS cache, which would have been fixed with an ipconfig /flushdns.  I seriously doubt that any changes would be required to the query plan for the procedure that the job calls, as the query plan would not go any further than a reference to the linked server (although, it may reference the linked server by ID, rather than name).  From the error itself (cannot locate RefreshDB in sys.servers), I would check on the stored procedure text, and make sure the name of the linked server called matches what is in sys.servers.

  • July 5, 2017 at 7:31 am

    #1949471

    So sorry for late reply - I was on holiday and unable to get access to the internet.
    I also initially had thought it was a local DNS cache issue. But in the end i discovered that the stored proc on Server A called another stored proc on Server B. This sproc on Server B also called a RefreshDatabases linked server but it did not exist on Server B. The reason why i didn't investigate the second stored proc is because the first stored proc failed so instantly that I assumed it failed in it's first call to RefreshDatabases locally on Server A.

    Thanks for taking an interest and responding 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply