60MXG
asked on
Create Linked Servers to Oracle Database
I am trying to create a linked server to connect to oracle database and when I run the select statement I got this error message "OLE DB provider "MSDAORA" for linked server "ORACLE" returned message "Error while trying to retrieve text for error ORA-12154". Any one has an idea?
I have used Orcle SQL Developer client and is able to access the Oracle database but just don't know how to set it up at the Microsoft SQL 2005 Server to link it. I used the SQL Plus and is able to access the database too and just don't know why it is not working on the link server. I have the Oracle Listener set up already but don't know what I should enter in the Linked Server Properties.
----------------- Linked Server Setting (having issue??)------------------
Linked server : ORACLE
Server type :
Other data source
Provider: Mcrosoft OLE DB Provider for Oralce
Product Name: (What should I enter) ??
Data source: (what should I enter)?? name of the database?? or the listener name??
Provider string: (what should I enter)?? Is this the connection string MyUsername/MyPassword@MyLi
Location: (what should I enter)??
Catalog: (what should I enter)?? Is this the Oracle Sid??
--------------------------
here is the login I used in the SQL Plus which works without a problem
Enter user-name: MyName/MyPassword@MyListen
--------------------------
Connection Name : MyConnection
Username : MyUsername
Password : **********
Oracle Setting
Hostname: OracleDb.abc.com
port: 1521
SID: Ora10
and I can connect to Oracle database with the SQL Developer
--------------------------
Well I think I am just need some help on the parts that I have ?? in the Linked Server Properties.
I have used Orcle SQL Developer client and is able to access the Oracle database but just don't know how to set it up at the Microsoft SQL 2005 Server to link it. I used the SQL Plus and is able to access the database too and just don't know why it is not working on the link server. I have the Oracle Listener set up already but don't know what I should enter in the Linked Server Properties.
----------------- Linked Server Setting (having issue??)------------------
Linked server : ORACLE
Server type :
Other data source
Provider: Mcrosoft OLE DB Provider for Oralce
Product Name: (What should I enter) ??
Data source: (what should I enter)?? name of the database?? or the listener name??
Provider string: (what should I enter)?? Is this the connection string MyUsername/MyPassword@MyLi
Location: (what should I enter)??
Catalog: (what should I enter)?? Is this the Oracle Sid??
--------------------------
here is the login I used in the SQL Plus which works without a problem
Enter user-name: MyName/MyPassword@MyListen
--------------------------
Connection Name : MyConnection
Username : MyUsername
Password : **********
Oracle Setting
Hostname: OracleDb.abc.com
port: 1521
SID: Ora10
and I can connect to Oracle database with the SQL Developer
--------------------------
Well I think I am just need some help on the parts that I have ?? in the Linked Server Properties.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes.
Yes, the ODBC connection is setup with the Net Config Client. That in turn is configured in the oracle TNSNAMES.ORA to allow you to link to an ORACLE listener.
ASKER
I created the linked server and ran a select statement and got this error!
OLE DB provider "ORAOLEDB.Oracle" for linked server "CDBP" returned message "Error while trying to retrieve text for error ORA-12154".
my select statement is like this
select * from CDBP..database.table
CDBP is the linked server, [database] is the owner of the table, and [table] is the table. I bump into this same problem and not sure what I did wrong.
OLE DB provider "ORAOLEDB.Oracle" for linked server "CDBP" returned message "Error while trying to retrieve text for error ORA-12154".
my select statement is like this
select * from CDBP..database.table
CDBP is the linked server, [database] is the owner of the table, and [table] is the table. I bump into this same problem and not sure what I did wrong.
ASKER
I created the linked server and ran a select statement and got this error!
OLE DB provider "ORAOLEDB.Oracle" for linked server "CDBP" returned message "Error while trying to retrieve text for error ORA-12154".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "CDBP".
Can this be security issue??
OLE DB provider "ORAOLEDB.Oracle" for linked server "CDBP" returned message "Error while trying to retrieve text for error ORA-12154".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "CDBP".
Can this be security issue??
Under the CDBP linked server, right click properties and check . "Allow inprocess" has to be checked in the provider options.
This should correct your issue.
This should correct your issue.
From SQL Server Management Studio-->Server Objects-->Linked Servers-->Providers-->CDBP
If this doesn't work, uncheck it again and save. It registers the settings in SQL.
If this doesn't work, uncheck it again and save. It registers the settings in SQL.
ASKER
hades666 I don't that already! I am looking into the security issue now!
ASKER
hades666. The Provider options setting has been done and still have problem.
ASKER
Got this figured out. In my ODBC Connection I needed to select this Driver "Oracle in OraClient11g_home1" driver which I installed. I was using "Microsoft ODBC for Oracle" and "Oracle RDB Driver".
The logic behide this is that I installed the "Oracle 11g client service" and have the "Net Manager" setup already.
I think my problem is not explained what I had installed on the server.
I will give credits just for the effort to help.
The logic behide this is that I installed the "Oracle 11g client service" and have the "Net Manager" setup already.
I think my problem is not explained what I had installed on the server.
I will give credits just for the effort to help.
ASKER
Giving points based on the effort of each contributors.
ASKER