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 !)

Improve this question
2
  • 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, 2016 at 14:16
  • @RLF - you should make that an answer.
    – Hannah Vernon ♦
    Feb 8, 2016 at 15:19

2 Answers 2

Reset to default
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

Improve this answer
3
  • 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, 2016 at 16:47
  • @grahamj42 agree
    – atokpas
    Feb 8, 2016 at 16:54
  • Of course, the answer is still "drop and recreate."
    – RLF
    Feb 8, 2016 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.