Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

[URGENT - PRODUCTION ISSUE]

All of a sudden, on a SQL Server 2005 instance, all the Linked Servers to Oracle stopped working. All other instances can connect to the linked servers without a problem using identical configurations.

When I right-click and "Test connection", I get:

Cannot initialize the data source object of OLE DV provider "OraOLEDB.Oracle" for linked server "servername".  
OLE DB provider "OraOLEDB.Oracle" for linked server "servername" returned message "*gibberish characters here*" (Microsoft SQL Server, Error: 7303)

The error is different gibberish characters every time I test the connection. (Squares and unreadable characters). I am using the Oracle ODAC 10 client on the SQL Server. Any ideas on how to fix this other than restarting the instance?

Thank you.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

Unfortunately, a reboot is most likely your only solution.

If you have older Oracle ODAC drivers (e.g. 10.2.0.1), they have been known to have issues with occasional corrupted links.

As I said, a reboot should fix it -- at least temporarily.

However, a more permanent fix would be to update your Oracle ODAC to at least 10.2.0.2 -- or better yet, use the latest 11g provider (they're backwards compatible with 10g).

http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

Good luck! --Dubs

share|improve this answer
add comment

If it's ALL Oracle linked servers for that instance then my first thought would be that something changed on the Oracle provider for that particular SQL instance. Start by verifying the Provider configuration between the broken instance and an instance in which the Oracle linked servers are working.

share|improve this answer
    
Everything is identical in both the provider configuration itself, and in the connection properties for all linked servers using that provider. –  SomeGuy Mar 1 '10 at 17:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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