When I tried to execute query I got the below error.
SELECT * FROM OPENQUERY([TESTORACLE], 'select * from TESTTABLE')
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TESTORACLE".
I have created DSN and used below query to create linked server.
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'TESTORACLE' ,@useself = 'False' ,@locallogin = NULL ,@rmtuser = 'USERNAME' ,@rmtpassword = 'PASSWORD'; EXEC sp_addlinkedserver @server = 'TESTORACLE' ,@srvproduct = 'ORCLELINK' ,@provider = 'MSDASQL' ,@datasrc = 'ORCLELINK'
After that I tested the linked server and it worked fine. When I execute a query it throws access denied exception so I enable Allow In process to the provider.
After that it throws above error and the test connection also not works.
Not sure why the connection fails when I enable Allow in process.
Please help me to resolve the error.