Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server to Oracle DB not working after installing OLEdb provider ( ODAC) Expand / Collapse
Author
Message
Posted Friday, June 29, 2012 8:43 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:31 PM
Points: 75, Visits: 345
Hello all,

Iève isntalled the oracle client along with data access components which isntalled the provider required to create a linked server to the Oracle DB. After configuring it it gives me this very vague message at test time.

------------------------------

Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxx". (Microsoft SQL Server, Error: 7302)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

I've configured correctly with the connection strings and valid user but not sure what is missing.
The same thing was done in a DEV env and it works towards the same Oracle DB

Thanks
Post #1323107
Posted Friday, June 29, 2012 11:45 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:31 PM
Points: 75, Visits: 345

UPDATE: I've clicked on the provider dynamic parameter and now get this error when querying the Oracle DB.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxxx" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "OraOLEDB.Oracle" for linked server "xxxxxx".


Thanks
Post #1323207
Posted Saturday, June 30, 2012 6:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:19 PM
Points: 1,390, Visits: 6,347
Did you also check "In Process" option? (should be enabled)
Post #1323447
Posted Saturday, June 30, 2012 8:13 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:31 PM
Points: 75, Visits: 345
Thanks for response... I found the fix and it was by logging in with a SQL login and NOT windows login. Not sure why but everything worked including updates towards oracle.

I guess its a security setting but I don't want to mess up anything with a prod server though.

J
Post #1323452
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse