Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

i am having a bit of a linked server problem .

I created a sql server linked server and it's been working fine for last 2 years and suddenly not working .

SQL Server Microsoft SQL Server 2005 - 9.00.5324.00 (X64) Aug 24 2012 18:28:47 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Oracle client version Oracle - OraClient11g_home1

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server

Ps : we have enabled Allow in Process in Sql Server for Oracle Provider

anyone got any ideas on this and what can be done to avoid this happen again

many thanks

Ray

share|improve this question
1  
You need to figure out what changed in the environment around that time (Windows Updates? Service pack application?). Things don't usually blow up by themselves without some kind of provocation. – Jon Seigel Aug 6 '13 at 16:58
    
We are talking about the Oracle client, It has been known to self-destruct. Tongue in cheek aside this could actually be a memory leak in the oracle client and the server might need to be restarted to clear it. Are there any indications in the SQL Server errorlog about client crashes or minidumps in the log directory? – Spörri May 11 '15 at 13:07

I was having same error when i was connecting SQL Server with MySQL the issue was due to wrong Username or password in Local Server login to remote server login mapping.

Check if its correct or not !

enter image description here

share|improve this answer

linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle Database Server using tnsping.

tnsping searched to the path C:\product\11.1.0\db_1\bin initially, this will initially use the used parameter file C:\product\11.1.0\db_1\network\admin\sqlnet.ora

tnsping successfully connected to the Oracle database from the SQL Server box. Then check the Provider details while creating the Linked Server.

The Linked Server provider was Oracle Provider for OLE DB. This is the driver that gets installed along with the Oracle Client Components.

Error 7302 can be a result of MSDAORA.dll not being registered correctly. In this case it had to be an issue with the dll related to Oracle Provider for OLE DB i.e.OraOleDB.dll. Before checking the dll related issues,to check if something was missing in the registry. The list of Providers in the Linked Server is populated from the Providers key in the registry.

Error 7303 means that the username and password combination is not correct, hence the login failed for the user specified in the Linked Server. When the correct login credentials for the Oracle database were entered, the Linked Server got created successfully!

share|improve this answer

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.