2

I have created a linked Oracle server in SQL Server 2012 using Oracle Provider for OLE DB and TNSNAMES.ORA (the Oracle database is clustered and, as far as I know, no other connection method works with clustered servers). This works fine.

Now the Oracle database has been moved to another cluster, so I edited TNSNAMES.ORA. Oracle SQL Developer works fine.

It seems that SQL Server doesn't re-read TNSNAMES.ORA but stores the connection details internally, because even after a server reboot all queries time out.

Does anyone know of a solution other than deleting and recreating the linked server ?

(That's what I did to resolve my issue, but it shouldn't be that hard !)

  • 2
    Yes, it is necessary to drop and recreate the linked server. This takes just moments. There is no reason to believe that SQL Server would try to read the internal contents of a different relational database. – RLF Feb 8 '16 at 14:16
  • @RLF - you should make that an answer. – Max Vernon Feb 8 '16 at 15:19
0

How you solved the issue is the way to solve it because we can't modify a Linked Server. And SQL Server doesnt read the tnsnames.ora.

Though the solution is drop and recreate we can use SQL Management Studio to edit the Drop and Create script as follows:
1. Right click on the linked server.
2. Choose Script Linked Server as.
3. Choose New Query Editor Window.
4. Now we can modify the script as our requirements and run.
This simply drops and recreates the Linked Server.

Thanks

  • If SQL Server didn't read TNSNAMES.ORA (by an API call, I suppose), I wouldn't have had a problem ! Oracle chose to put the connection details in an external file to simplify this sort of change, and Microsoft ignores it. – grahamj42 Feb 8 '16 at 16:47
  • @grahamj42 agree – JSapkota Feb 8 '16 at 16:54
  • Of course, the answer is still "drop and recreate." – RLF Feb 8 '16 at 19:08
0

Yes, it is necessary to drop and recreate the linked server. This takes just moments, so the hit is very small.

Likewise, there is no reason to believe that SQL Server would try to read the internal contents of a different relational database.

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.