Thursday, March 5, 2015

Cannot create an instance of OLE DB db provider "oraOLEDB.Oracle" for linked server "ORACLE_LINK"


If you are getting below issue : 

After creating linked server and when you try to run the query using ssms 
select * from  OpenQuery(ORACLE_LINK, 'select * from xxx_table_history') 
you get encounter below issue.  

Cannot create an instance of OLE DB db provider "oraOLEDB.Oracle" for linked server "ORACLE_LINK" 

or

Execute permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys' (Microsoft SQL Server, Error 229)



Resolution : 

Login to the server where you are getting this error go to 

Server Objects --> Linked servers --> Providers --> right click on [OraOLEDB.Oracle] and select properties 


In properties window click on ALLOW INPROCESS