I want to link Oracle database with SQL server. I have used SQL Server's builtin LinkedServer SPs but it returned the following error.
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "OracleLinkedServer".
I have used the following commands in SQL Server Express 2008 to create a linked server.
EXEC sp_addlinkedserver 'OracleLinkedServer', 'Oracle', 'MSDAORA', 'OracleServer'
EXEC sp_addlinkedsrvlogin 'OracleLinkedServer', false, 'sa', 'system', '123456'
select * from OracleLinkedServer..system.CIL_NOTIFICATION
Thanks in advance for helping me.
I have found the solution of this problem. The version of Oracle application, which I have installed, is 11gR1
My Oracle database was not started and mounted, so I used startup command
Then I have checked the listener of the database, It should be started.
Then I have modified the statements mentioned in the question. Here is the modification.
--add a linked server into SQL server
--parameters: serverName, ServerProduct, ProviderName, DataSource / Listener Name of desired database
EXEC sp_addlinkedserver 'OracleLinkedServer', 'Oracle', 'MSDAORA', 'PC-2.my.xgen/nb'
--add login information into linked server
EXEC sp_addlinkedsrvlogin 'OracleLinkedServer', false, Null, 'system', '123456'
select * from OracleLinkedServer..SYSTEM.CIL_NOTIFICATION
It returns all the records from the Oracle database table CIL_NOTIFICATION