0

[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.

SomeGuy
  • 387
  • 1
  • 3
  • 16

3 Answers3

3

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

Dubs
  • 188
  • 6
0

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.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • Everything is identical in both the provider configuration itself, and in the connection properties for all linked servers using that provider. – SomeGuy Mar 01 '10 at 17:09
-2

un checked “Allow in Process” property of the provider. Articles Permissions needed to set up linked server with out-of-process provider :http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx

Nadir
  • 1