Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
OLEDB provider issue

newbiegal
Member Posts: 396
Hi all,
I'm setting up linked server from sql server to oracle. I installed Oracle client 10.2.0.1 on the server, configured ODBC to the Oracle database using OraOLEDB.Oracle provider. Created linked server on sql server, but getting the below error:
SELECT * FROM
testlink..user.table
OLE DB provider "ORAOLEDB.ORACLE" for linked server "TestLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "ORAOLEDB.ORACLE" for linked server "TestLink".
tnsping to the oracle database works from the server and the ODBC connection testlink to the oracle database is successful. I tried checking Allow inprocess in the provider option by right clicking linked server properties on the management studio also.. Nothing seemed to work..
Please help.. thanks
I'm setting up linked server from sql server to oracle. I installed Oracle client 10.2.0.1 on the server, configured ODBC to the Oracle database using OraOLEDB.Oracle provider. Created linked server on sql server, but getting the below error:
SELECT * FROM
testlink..user.table
OLE DB provider "ORAOLEDB.ORACLE" for linked server "TestLink" returned message "ORA-12154: TNS:could not resolve the connect identifier specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "ORAOLEDB.ORACLE" for linked server "TestLink".
tnsping to the oracle database works from the server and the ODBC connection testlink to the oracle database is successful. I tried checking Allow inprocess in the provider option by right clicking linked server properties on the management studio also.. Nothing seemed to work..
Please help.. thanks
Answers
-
Hi,
TNS_ADMIN used in command prompt is somehow ignored (added comment afterwards: mapped drive used in TNS_ADMIN may not be accessable, use UNC path in TNS_ADMIN instead).
Create TNSNAMES entry through Oracle Net Configuration Assistant.
Test Connection on Linked server must work before any SELECT can work.
To use insert from linked server data change provider options:
Allow inprocess = checked
Restart SQL server windows service and try again.
Best regards,
Alex
Edited by: user4580124 on Dec 15, 2010 1:40 PM
Edited by: user4580124 on Nov 15, 2011 2:56 AM -
Thanks Alex. I installed Oracle client 10.2.0.1, created linked server and followed the steps above and it worked. But get the following error for some selects..
SELECT * FROM
testlink..user.table
Invalid data for type "numeric".
I researched on this error that it is fixed in ODAC 10.2.0.2.21. I first installed Oracle client (Oracle10g_client_64bit.zip) and then tried installing ODAC (Oracle 10g Release 2 ODAC 10.2.0.2.21) but get a "runtime error" during the install.. Not sure if I'm missing something...
Please help -
Hi,
Whenever I detect strange behavior of an Oracle installation I first check the paths:
Installation folder and destination paths should not have blanks or empty space characters.
I can tell you for 11g x64 client installation. It does not work if the installation folder contains blank.
Do you have only problem with numeric tables? How about tables with string values only?
Best regards,
Alex -
You get this error because the Oracle data dictionary is case sensitive. Oracle object names are all uppercase by default. The linked-server query must exactly match the case of all referenced Oracle metadata, such as schema and table names. So, the following query works:
SELECT * FROM oradb..SCOTT.DEPT WHERE deptno = 10
This discussion has been closed.